Hugo Hugo - 2 months ago 7
MySQL Question

Extremely slow LEFT JOIN on specific table

I am having trouble finding out why my query is extremely slow; 60 seconds on a Dual Xeon L5630 with 48GB DDR3 running Ubuntu 16.04 with PHP7.0-FPM and MariaDB 10.0.27

SELECT v.video_id, v.user_id, v.title, v.slug, v.rating, v.rated_by,
v.duration, v.thumb, v.total_views, v.total_comments, v.add_time,
v.view_time, v.status, v.source_id, v.orientation, v.thumbs,
v.featured, v.flagged,
u.username,
s.name,
f.reason,
GROUP_CONCAT(c.name) AS categories
FROM video AS v
LEFT JOIN video_flags AS f ON (f.video_id = v.video_id)
LEFT JOIN video_sources AS s ON (s.source_id = v.source_id)
LEFT JOIN user AS u ON (u.user_id = v.user_id)
LEFT JOIN video_category AS vc ON (vc.video_id = v.video_id)
LEFT JOIN video_categories AS c ON (c.category_id = vc.category_id) GROUP BY v.video_id ORDER BY v.video_id DESC LIMIT 10


I've pinpointed the problem to be in the video_flags table, because when I comment the f.reason field and the left join on video_flags, the query only takes 152ms. The video_flags table has an index on video_id and the field type is the same in both tables INT(11)

When I run explain select I get the following back:

+------+-------------+-------+--------+---------------+----------+---------+----------------------------+---------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------+----------+---------+----------------------------+---------+-------------------------------------------------+
| 1 | SIMPLE | v | ALL | NULL | NULL | NULL | NULL | 1219933 | Using temporary; Using filesort |
| 1 | SIMPLE | f | ALL | video_id | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | s | eq_ref | PRIMARY | PRIMARY | 4 | adb_network.v.source_id | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | adb_network.v.user_id | 1 | |
| 1 | SIMPLE | vc | ref | video_id | video_id | 4 | adb_network.v.video_id | 2 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | adb_network.vc.category_id | 1 | Using where |
+------+-------------+-------+--------+---------------+----------+---------+----------------------------+---------+-------------------------------------------------+


I don't know what I'm missing here, first I thought it had to something with the video_flags table being empty, then I added a record and the query was quick (200ms) but now the problem is back and the query is taking forever to complete again.

Any help is much appreciated.




Update: added the explain select without the f.reason column for @somnium:

+------+-------------+-------+--------+---------------+----------+---------+----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------+----------+---------+----------------------------+------+-------------+
| 1 | SIMPLE | v | index | NULL | PRIMARY | 4 | NULL | 5 | |
| 1 | SIMPLE | f | ref | video_id | video_id | 4 | adb_network.v.video_id | 1 | Using index |
| 1 | SIMPLE | s | eq_ref | PRIMARY | PRIMARY | 4 | adb_network.v.source_id | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | adb_network.v.user_id | 1 | |
| 1 | SIMPLE | vc | ref | video_id | video_id | 4 | adb_network.v.video_id | 2 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | adb_network.vc.category_id | 1 | Using where |
+------+-------------+-------+--------+---------------+----------+---------+----------------------------+------+-------------+





Solution: As suggested by @somnium I tried adding a
FORCE INDEX
on the
video_id
column and that has brought the query time down from 60 seconds to 272ms - still not sure why it would lose the index during a join but problem is solved. Thanks

SELECT v.video_id, v.user_id, v.title, v.slug, v.rating, v.rated_by,
v.duration, v.thumb, v.total_views, v.total_comments, v.add_time,
v.view_time, v.status, v.source_id, v.orientation, v.thumbs,
v.featured, v.flagged,
u.username,
s.name,
f.reason,
GROUP_CONCAT(c.name) AS categories
FROM video v
LEFT JOIN video_flags f FORCE INDEX FOR JOIN (video_id) ON (f.video_id = v.video_id)
LEFT JOIN video_sources s ON (s.source_id = v.source_id)
LEFT JOIN user u ON (u.user_id = v.user_id)
LEFT JOIN video_category vc ON (vc.video_id = v.video_id)
LEFT JOIN video_categories c ON (c.category_id = vc.category_id) GROUP BY v.video_id ORDER BY v.video_id DESC LIMIT 10

Answer

You are accidentally causing a full table scan over a large table videos.

Hypothesis

Looking at your explain without f.reason, the optimizer will ignore the video_flags table. This allows MySQL/MariaDB to fully utilize all indices.

When adding f.reason, MySQL now needs to match v.video_id = f.video_id. As video_flags has one row, MySQL will attempt to retrieve v.video_id for every entry in video. It looks like you don't have an index on v.video_id. Therefore MySQL will have to scan the full videos table from the disk/memory in order to obtain video_id. This results in 1219933 rows retrieve (compared to 5 in the explain select without video_flags).

Potential solution

Try adding an index on v.video_id in order to speed up the lookups. Carefully check both explain selects to find which indices are suddenly not used. Note NULL for possible_keys for table v in your slow select.

Hope that helps.

Comments