mardi 2 décembre 2014

Why wouldnt Mysql use index on the Primary query in a nested query


I have two tables: search_criteria and pricing.


There is an index on search_id column in search_criteria table and on pricing_id column in pricing table.


But running this nested query does not use index on search_criteria table.



explain
select *
from search_criteria USE INDEX (idx_search_id)
where search_id in
(select search_id
from pricing
where pricing_id = '009330be-d041-444f-a624-ca652f3f61ed');


+----+--------------------+---------------------+------+------------------------------+----------------+---------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------------+------+------------------------------+----------------+---------+-------+----------+-------------+
| 1 | PRIMARY | search_criteria | ALL | NULL | NULL | NULL | NULL | 19582252 | Using where |
| 2 | DEPENDENT SUBQUERY | pricing | ref | idx_pricing_id,idx_search_id | idx_pricing_id | 36 | const | 1 | Using where |
+----+--------------------+---------------------+------+------------------------------+----------------+---------+-------+----------+-------------+


If I use this table without the nested query it uses the index



explain extended select * from search_criteria where search_id in ('36afabcc-e896-48b6-ad0f-c683845d4a4f')

+----+-------------+---------------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | search_criteria | ref | idx_search_id | idx_search_id | 103 | const | 1 | 100.00 | Using where |


Why is this happening ?


MYSQL VARIABLES



+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 1.1.8 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.16-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+




Aucun commentaire:

Enregistrer un commentaire