How does Index Scope work in Mysql?

Question

The MySQL manual has a page in the index hint that mentions that you can specify the index hint for specific parts of the query.

You can specify the index hint area by adding a FOR clause to the hint. This provides finer-grained control over the choice of an optimizer for the execution plan for the various stages of query processing. To affect only the indexes used when MySQL decides how to find rows in a table and how to handle joins, use FOR JOIN . To influence the use of an index to sort or group rows, use FOR ORDER BY or FOR GROUP BY .

However, information on how this works or what it actually does in the MySQL optimizer is a bit more. Also in practice, this seems insignificant, actually improving something.

Here is a test request and an explanation says about the request:

 SELECT `property`.`primary_id` AS `id` FROM `California` `property` USE INDEX FOR JOIN (`Zipcode Bedrooms`) USE INDEX FOR ORDER BY (`Zipcode Bathrooms`) INNER JOIN `application_zipcodes` `az` ON `az`.`application_id` = '18' AND `az`.`zipcode` = `property`.`zipcode` WHERE `property`.`city` = 'San Jose' AND `property.`zipcode` = '95133' AND `property`.property_type` = 'Residential' AND `property`.`style` = 'Condominium' AND `property`.`bedrooms` = '3' ORDER BY `property`.`bathrooms` ASC LIMIT 15 ; 

I explain:

 EXPLAIN SELECT `property`.`primary_id` AS `id` FROM `California` `property` USE INDEX FOR JOIN (`Zipcode Bedrooms`) USE INDEX FOR ORDER BY (`Zipcode Bathrooms`) INNER JOIN `application_zipcodes` `az` ON `az`.`application_id` = '18' AND `az`.`zipcode` = `property`.`zipcode` WHERE `property`.`city` = 'San Jose' AND `property.`zipcode` = '95133' AND `property`.property_type` = 'Residential' AND `property`.`style` = 'Condominium' AND `property`.`bedrooms` = '3' ORDER BY `property`.`bathrooms` ASC LIMIT 15\g +------+-------------+----------+--------+---------------+---------+---------+------------------------------------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+--------+---------------+---------+---------+------------------------------------+------+----------------------------------------------------+ | 1 | SIMPLE | Property | ref | Zip Bed | Zip Bed | 17 | const,const | 2364 | Using index condition; Using where; Using filesort | | 1 | SIMPLE | az | eq_ref | PRIMARY | PRIMARY | 7 | const,Property.zipcode | 1 | Using where; Using index | +------+-------------+----------+--------+---------------+---------+---------+------------------------------------+------+----------------------------------------------------+ 2 rows in set (0.01 sec) 

So, to summarize, I basically wonder how the index area will be used, since it does nothing when I add or remove the row USE INDEX FOR ORDER BY (Zipcode Bathrooms) .

Answer 1

I have yet to figure out how to use a few tips. MySQL will almost never use more than one index per SELECT . The only exception that I know of is "index merging", which doesn't matter in your example.

The optimizer usually focuses on finding a good index for the WHERE . If it completely covers WHERE , without any "ranges", it checks to see if <23> and ORDER BY fields are in the correct order. If it can handle all WHERE, GROUP BY, and ORDER BY , then it can actually optimize LIMIT (but not OFFSET ).

If the optimizer cannot use all of the WHERE , it may end up in ORDER BY in the hope of avoiding the "filesort", which otherwise requires ORDER BY .

None of this allows using different indexes for different sentences. One hint may facilitate the use of one of the above cases (see above) compared to another; I dont know.

Do not use utf8 for zipcode; it makes things more bulky than necessary (3 bytes per character). In general, reducing the size of the table will help some. Or, if you have a huge dataset, this can help a lot. (Avoiding I / O is very important.)

Bathrooms not very selective; there is nothing to win, even if it were possible.

az.application_id - the big monkey key in the request; what is it?