Hi,
I have a database with a few tables.
module (360k rows)
format
author
path
collect
each module has an author a format and possible a path and collect and i join them like
SELECT module_name,author.author_id,author_name,format.format_id,format_name,collect.collect_id,collect_name,module_size,path_name FROM module JOIN author ON module.author_id=author.author_id JOIN format ON module.format_id=format.format_id LEFT OUTER JOIN collect ON module.collect_id=collect.collect_id LEFT OUTER JOIN path ON module.path_id=path.path_id
If I do a LIKE lookup on author name
SELECT module_name,author.author_id,author_name,format.format_id,format_name,collect.collect_id,collect_name,module_size,path_name FROM module JOIN author ON module.author_id=author.author_id JOIN format ON module.format_id=format.format_id LEFT OUTER JOIN collect ON module.collect_id=collect.collect_id LEFT OUTER JOIN path ON module.path_id=path.path_id WHERE ( author_name LIKE '%drax%' ) ORDER BY module_name ASC LIMIT 50,50
it can take about 5 seconds to complete.
explain says:
+----+-------------+---------+--------+---------------------+-----------+---------+---------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+---------------------+-----------+---------+---------------------------+------+---------------------------------+
| 1 | SIMPLE | format | ALL | PRIMARY | NULL | NULL | NULL | 354 | Using temporary; Using filesort |
| 1 | SIMPLE | module | ref | author_id,format_id | format_id | 2 | modland.format.format_id | 1089 | |
| 1 | SIMPLE | author | eq_ref | PRIMARY | PRIMARY | 2 | modland.module.author_id | 1 | Using where |
| 1 | SIMPLE | collect | eq_ref | PRIMARY | PRIMARY | 2 | modland.module.collect_id | 1 | |
| 1 | SIMPLE | path | eq_ref | PRIMARY | PRIMARY | 2 | modland.module.path_id | 1 | |
+----+-------------+---------+--------+---------------------+-----------+---------+---------------------------+------+---------------------------------+
however adding a subquery like so:
SELECT module_name,author.author_id,author_name,format.format_id,format_name,collect.collect_id,collect_name,module_size,path_name FROM module JOIN author ON module.author_id=author.author_id JOIN format ON module.format_id=format.format_id LEFT OUTER JOIN collect ON module.collect_id=collect.collect_id LEFT OUTER JOIN path ON module.path_id=path.path_id INNER JOIN (SELECT author_id from author WHERE ( author_name LIKE '%drax%' ) ) AS tempauthor ON tempauthor.author_id = module.author_id ORDER BY module_name ASC LIMIT 50,50
and it completes much faster
explain says:
+----+-------------+------------+--------+---------------------+-----------+---------+---------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------------+-----------+---------+---------------------------+-------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 18 | Using temporary; Using filesort |
| 1 | PRIMARY | author | eq_ref | PRIMARY | PRIMARY | 2 | tempauthor.author_id | 1 | |
| 1 | PRIMARY | module | ref | author_id,format_id | author_id | 2 | tempauthor.author_id | 18 | |
| 1 | PRIMARY | format | eq_ref | PRIMARY | PRIMARY | 2 | modland.module.format_id | 1 | |
| 1 | PRIMARY | collect | eq_ref | PRIMARY | PRIMARY | 2 | modland.module.collect_id | 1 | |
| 1 | PRIMARY | path | eq_ref | PRIMARY | PRIMARY | 2 | modland.module.path_id | 1 | |
| 2 | DERIVED | author | ALL | NULL | NULL | NULL | NULL | 21548 | Using where |
+----+-------------+------------+--------+---------------------+-----------+---------+---------------------------+-------+---------------------------------+
is it logical that the second query should be faster? it seems silly that I need a create derived table alias (is that the correct terminology) to speed it up.
Any guidance or further information would be useful. Ideally I would prefer to use the much simple query. After all the optimiser should handle things for me right?
Edited 1 time(s). Last edit at 12/24/2008 12:17PM by jools smyth.