MySQL Forums :: Newbie :: Query performance on database with joins


Advanced Search

Query performance on database with joins
Posted by: jools smyth ()
Date: December 24, 2008 11:41AM

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.

Options: ReplyQuote


Subject Written By Posted
Query performance on database with joins jools smyth 12/24/2008 11:41AM
Re: Query performance on database with joins Rick James 12/24/2008 05:38PM
Re: Query performance on database with joins jools smyth 12/24/2008 06:45PM
Re: Query performance on database with joins Rick James 12/24/2008 10:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.