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

Question

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                     |
+-------------------------+------------------------------+

After Jynus reply, I have modified the question.

Executed Query :

explain 
  select s_c.*  
  from search_criteria s_c USE INDEX (idx_search_id) 
  JOIN pricing p USING (search_id)
  WHERE p.pricing_id = '009330be-d041-444f-a624-ca652f3f61ed';



    ----+-------------+-------+------+------------------------------+----------------+---------+-------+----------+--------------------------------+
| id | select_type | table | type | possible_keys                | key            | key_len | ref   | rows     | Extra                          |
+----+-------------+-------+------+------------------------------+----------------+---------+-------+----------+--------------------------------+
|  1 | SIMPLE      | p     | ref  | idx_pricing_id,idx_search_id | idx_pricing_id | 36      | const |        1 | Using where                    |
|  1 | SIMPLE      | s_c   | ALL  | NULL                         | NULL           | NULL    | NULL  | 19663904 | Using where; Using join buffer |
+----+-------------+-------+------+------------------------------+----------------+---------+-------+----------+--------------------------------+

Show create sql of search_criteria;

CREATE TABLE `search_criteria` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `search_id` varchar(100) DEFAULT NULL,
 `origin` varchar(45) NOT NULL,
 `dest` varchar(45) NOT NULL,
 `adults` int(1) NOT NULL,
 `children` int(1) NOT NULL,
 `infants` int(1) NOT NULL,
 `trip_type` varchar(1) NOT NULL,
 `flight_type` enum('INT','DOM') DEFAULT NULL,
 `depart_date` datetime DEFAULT NULL,
 `arrival_date` datetime DEFAULT NULL,
 `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `idx_search_id` (`search_id`),
 KEY `idx_created` (`created_on`)
)ENGINE=InnoDB AUTO_INCREMENT=288339047 DEFAULT CHARSET=latin1 |


 CREATE TABLE `pricing` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pricing_id` char(36) CHARACTER SET latin1 NOT NULL,
  `search_id` varchar(100) DEFAULT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `supplier_codes` varchar(64) DEFAULT NULL,
  `price` double DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `idx_pricing_id` (`pricing_id`),
   KEY `idx_created_on` (`created_on`),
   KEY `idx_search_id` (`search_id`),
) ENGINE=InnoDB AUTO_INCREMENT=9264209 DEFAULT CHARSET=utf8 |

Answer 1

You are trying to execute a semijoin. This is a very well know issue with the MySQL optimizer before MySQL 5.6. The only way MySQL knows how to execute it is to perform a full table scan on the left table and execute the inner query once per row, thus it is unable to use the index.

You have several alternatives:

• Migrate to 5.6 (or MariaDB 5.5): it will be optimized correctly (I think in this case, converted to a JOIN transparently)

• Rewrite the query as a scalar subquery (this only works if you expect to receive only one value, that is, pricing_id is unique):

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

• Rewrite the query as a JOIN. This only works in some semijoins (those where you will not match more than one row at a time), but I think this is the case for you:

  explain 
  select s_c.*  
  from search_criteria s_c USE INDEX (idx_search_id) 
  JOIN pricing p USING (search_id)
  WHERE p.pricing_id = '009330be-d041-444f-a624-ca652f3f61ed';
  

By the way, once you have changed the query, you will not need the USE INDEX.

Answer 2

The reason why the index on search_id is not even considered is because the two tables - and therefore the two search_id columns - have different character sets. One has CHARSET = latin1 and the other CHARSET = utf8. The types are the same varchar(100) but charsets differ and that matters. Columns that are used in joins or comparisons should have identical datatypes and charsets (and collations), otherwise indexes become useless.

You should either alter the charset of one of the two tables or just one of the two columns so the character sets are matching.

As @jynus has already suggested, a composite index on (pricing_id, search_id) should also improve the plan and the efficiency of the queries.