Download - All IT eBooks
Transcript
key: PRIMARY key_len: 4 ref: NULL rows: 5136 filtered: 100.00 Extra: Using where; Using index *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: items_links type: index_subquery possible_keys: iid,iid_2 key: iid key_len: 5 ref: func rows: 1 filtered: 100.00 Extra: Using index; Using where 2 rows in set, 1 warning (0.09 sec) The query execution plan is the same—and it is going to examine 5,136 rows for just six links! Is there any way to rewrite the query so it will execute faster? The type of subquery is index_subquery. This means that the optimizer uses an index lookup function that replaces the subquery completely. The output from SHOW WARNINGS shows how the query has been rewritten: mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select count(0) AS `count(*)` from `collaborate2011`.`items` where <in_optimizer>(`collaborate2011`.`items`.`id`,<exists> (<index_lookup>(<cache>(`collaborate2011`.`items`.`id`) in items_links on iid where (<cache>(`collaborate2011`.`items`.`id`) = `collaborate2011`.`items_links`.`iid`)))) 1 row in set (0.00 sec) The output is intimidating, but at least we can see some kind of join here. What if we rewrite the query to be more explicit about the columns on which the join is performed? We will also rewrite the subquery into an explicit JOIN; with current versions of MySQL, this method can dramatically improve performance: mysql> \W Show warnings enabled. mysql> EXPLAIN EXTENDED SELECT count(*) FROM items JOIN items_links ON (items.id=items_links.iid)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: items_links type: index possible_keys: iid,iid_2 key: iid_2 key_len: 5 Slow Queries | 27