Announcement

Announcement Module
Collapse
No announcement yet.

SQL query optimalization for N:M relation

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL query optimalization for N:M relation

    Dear all,

    I have a problem with one SQL query. I want to list all records from one table (in example bellow: "table1") matching some conditions - which are stored in "table2".

    The structure is attached to this post:


    The problematic query follows:
    SELECT DISTINCT table1.*
    FROM table1
    INNER JOIN resttyperest ON table2.table1_id=table1.id
    WHERE table2.table3_id IN (6, 10, 15)
    ORDER BY restaurant.name;

    This query uses temporary table and also filesort.
    So I tried to rewrite it to:

    SELECT table1.*
    FROM table1
    WHERE
    (SELECT id FROM table2
    WHERE table2.table3_id IN (6, 10, 15)
    AND table2.table1_id=table1.id
    ) IS NOT NULL
    ORDER BY table1.name

    This query uses only filesort and also runs faster (10-times) ).

    1) Are there any cons if I decide to use the second query with subqueries (on MySQL 5.0.x)?

    2) Do you have any ideas how to improve the second query?

    3) Do you use similar table-structure, if you have records (table1), types (table3) and their relation is N:M?

    4) Any other suggestions, notes? )

    Thanks...

  • #2
    A suggestion is that you post the create table statements also. That way we can all easily create the same tables and we can also directly see which indexes you have created.
    And if you want to rewrite your query before posting it, you should check better for spelling errors etc before posting it.

    But my suggestions are:
    1.
    Do you actually need DISTINCT?
    Isn't the records unique withing table1 already?

    2.
    Your original query should look like this:

    SELECT table1.*FROM table1INNER JOIN resttyperest ON ( table2.table1_id=table1.id AND table2.table3_id IN (6, 10, 15) )ORDER BY restaurant.name


    3.
    And then I suggest index:
    table2(table3_id, table1_id)
    which combined with the primary key id for table1 should find the records very fast. It would still need the filesort.

    4
    But possibly with index:
    table1(name, id))
    you should be able to avoid the filesort also.

    Comment


    • #3
      Hi Sterin...

      the correct SQL queries follows:


      SELECT DISTINCT table1.*FROM table1 INNER JOIN table2 ON (table2.table1_id=table1.id)WHERE table2.table3_id IN (6, 10, 15)ORDER BY table1.name


      The query was rewritten to:

      SELECT table1.*FROM table1WHERE (SELECT COUNT(id) FROM table2 WHERE table2.table3_id IN (6, 10, 15) AND table2.table1_id=table1.id ) > 0ORDER BY table1.name


      1) Yes, I need DISTINCT in the first query. Why? Because one record from table1 can be "joined" with many rows in "table2". But I want to get a row from table1 only once (and only if it fulfills conditions from table2). Without DISTINCT I get:
      id | name
      1 | Name1
      1 | Name1
      1 | Name1
      5 | Name5
      5 | Name5
      ...

      2) I tried to move that condition from WHERE clause to ON clause, but it seems to be considered as the same query in MySQL (time spent on this query is equal and EXPLAIN gives the same result)

      3) Currently I have the indices on:

      a) table1(id) b) table1(name) c) table2(table1_id) d) table2(table3_id) e) table2(id, table1_id, table3_id) f) table3(id)


      Do you think, that I should add table2(table3_id, table1_id)? Maybe it is in a duplicity to indices c) and d).

      4) Yes, the same question: I already have table1(id) and table1(name). Could your index improve performance of that query?

      Comment


      • #4
        Mysql will only use one index from each table in a query - so your indexes (c) and (d) are not the same as having the two column index sterin suggests.

        That two column index will probably make your original query fast enough. However, I think to avoid a filesort, the table1 index will need to be (id,name) rather than (name,id) - mysql will need the ID to join on before it uses the name to order by.

        Comment

        Working...
        X