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