MySQL: Optimized join across tables

1
votes

That query is slow (about 1.5 seconds). It should simply return a list of posts 

SELECT SQL_NO_CACHE p.id 
    FROM 1_posts p 
    LEFT JOIN 1_topics t ON (p.cid = t.cid AND p.container = t.id) 
    WHERE t.cid = 1010699;

EXPLAIN gives:

+----+-------------+-------+------+-----------------------+-----------+---------+---------------+------+--------------------------+
| id | select_type | table | type | possible_keys         | key       | key_len | ref           | rows | Extra                    |
+----+-------------+-------+------+-----------------------+-----------+---------+---------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | PRIMARY,cid           | cid       | 4       | const         |  216 | Using where; Using index |
|  1 | SIMPLE      | p     | ref  | PRIMARY,cid,container | container | 4       | forumdb.t.id |   49 | Using where              |
+----+-------------+-------+------+-----------------------+-----------+---------+---------------+------+--------------------------+

Note that SQL_NO_CACHE is there for testing purposes only.

Partial structure of tables:

1_posts:

mysql> explain 1_posts;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| cid            | int(20) unsigned     | NO   | PRI | 0       |                |
| id             | int(20) unsigned     | NO   | PRI | NULL    | auto_increment |
| container      | int(20) unsigned     | NO   | MUL | 0       |                |

mysql> explain 1_topics;
+-----------------+----------------------+------+-----+---------+----------------+
| Field           | Type                 | Null | Key | Default | Extra          |
+-----------------+----------------------+------+-----+---------+----------------+
| cid             | int(10) unsigned     | NO   | PRI | 0       |                |
| id              | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| container       | int(20)              | NO   | MUL | 0       |                |

Table posts has many millions of entries, topics about a 500k.

cid and id, on both tables are primary combo keys. cid means Community ID. These tables host many different forums.

Thank you for any help you could give with speeding this stuff up.

2
mysqloptimizationjoinquery-optimization
Are you sure the explain plan is from that query? I don't see why it would have Using temporary; Using filesort. – Vatev
At first sight, it is most unexpected the query took so long. Even if there is a 500k entries in the table post, according to the query execution plan, by using your indexes, MySQL has only to examine a few hundred rows. Are you alone on a "test" DB -- or are you running that query on a live system with concurrent access to your tables? – Sylvain Leroux
@SylvainLeroux the reason could be overall server load, but it might also be the case that t.cid = 1010699 is an edge case that has a lot more rows than the average (which explain shows). – Vatev
EDITED: THE EXPLAIN WAS WRONG. ADDED THE CORRECT EXPLAIN OUTPUT. – Segolene
That's why I don't understand why the query takes so long. Maybe the combo indexes are the issue? – Segolene

2 Answers

1
votes

When JOIN-ing on multiple fields (with AND) it helps to have a composite index on those fields.

In your case you can change your cid index in posts to (cid, container), that way mysql will be able to lookup the rows directly (you can still use it for queries with only cid conditions).

Also you should probably make it an INNER JOIN (as @AndrewK noted), for better readability.

0
votes

Just by looking at your query you can simplify it.

Since you are reducing the set to where t.cid = 1010699 you are stating that it cannot bet null. So you can use an inner join.

SELECT SQL_NO_CACHE p.id 
FROM 1_posts p 
JOIN 1_topics t ON p.container = t.id 
WHERE t.cid = 1010699 AND p.cid = t.cid;

EDIT:

As Vatev pointed out removing p.cid = t.cid does change the results of the query. I added it back in the WHERE clause for readability