mysql为什么用索引_为什么MySQL不使用最佳索引

我正在尝试优化查询,但是,MySQL似乎在查询上使用了非最佳索引,而且我似乎无法弄清楚出了什么问题.我的查询如下：

SELECT SQL_CALC_FOUND_ROWS deal_ID AS ID,dealTitle AS dealSaving,

storeName AS title,deal_URL AS dealURL,dealDisclaimer,

dealType, providerName,providerLogo AS providerIMG,createDate,

latitude AS lat,longitude AS lng,'local' AS type,businessType,

address1,city,dealOriginalPrice,NULL AS dealDiscountPercent,

dealPrice,scoringBase, smallImage AS smallimage,largeImage AS image,

storeURL AS storeAlias,

exp(-power(greatest(0,

abs(69.0*DEGREES(ACOS(0.82835377099147 *

COS(RADIANS(latitude)) * COS(RADIANS(-118.4-longitude)) +

0.56020534635454*SIN(RADIANS(latitude)))))-2),

2)/(5.7707801635559)) *

scoringBase * IF(submit_ID IN (18381),

IF(businessType = 1,1.3,1.2),IF(submit_ID IN (54727),1.19, 1)

) AS distance

FROM local_deals

WHERE latitude BETWEEN 33.345362318841 AND 34.794637681159

AND longitude BETWEEN -119.61862872928 AND -117.18137127072

AND state = 'CA'

AND country = 'US'

ORDER BY distance DESC

LIMIT 48 OFFSET 0;

列出表上的索引将显示：

+-------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| local_deals | 0 | PRIMARY | 1 | id | A | 193893 | NULL | NULL | | BTREE | | |

| local_deals | 0 | unique_deal_ID | 1 | deal_ID | A | 193893 | NULL | NULL | | BTREE | | |

| local_deals | 1 | deal_ID | 1 | deal_ID | A | 193893 | NULL | NULL | | BTREE | | |

| local_deals | 1 | store_ID | 1 | store_ID | A | 193893 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | storeOnline_ID | 1 | storeOnline_ID | A | 3 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | storeChain_ID | 1 | storeChain_ID | A | 117 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | userProvider_ID | 1 | userProvider_ID | A | 5 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | expirationDate | 1 | expirationDate | A | 3127 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | createDate | 1 | createDate | A | 96946 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | city | 1 | city | A | 17626 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | state | 1 | state | A | 138 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | zip | 1 | zip | A | 38778 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | country | 1 | country | A | 39 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | latitude | 1 | latitude | A | 193893 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | longitude | 1 | longitude | A | 193893 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | eventDate | 1 | eventDate | A | 4215 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | isNowDeal | 1 | isNowDeal | A | 3 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | businessType | 1 | businessType | A | 5 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | dealType | 1 | dealType | A | 5 | NULL | NULL | YES | BTREE | | |

| local_deals | 1 | submit_ID | 1 | submit_ID | A | 5 | NULL | NULL | YES | BTREE | | |

+-------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

运行说明扩展显示：

+------+-------------+-------------+------+----------------------------------+-------+---------+-------+-------+----------+----------------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+------+-------------+-------------+------+----------------------------------+-------+---------+-------+-------+----------+----------------------------------------------------+

| 1 | SIMPLE | local_deals | ref | state,country,latitude,longitude | state | 35 | const | 52472 | 100.00 | Using index condition; Using where; Using filesort |

+------+-------------+-------------+------+----------------------------------+-------+---------+-------+-------+----------+----------------------------------------------------+

表格中大约有20万行.奇怪的是它忽略了纬度和经度索引,因为它们应该更多地过滤表.在删除命令的“州”和“国家”的地方运行查询,可以得到以下解释：

+------+-------------+-------------+-------+--------------------+-----------+---------+------+-------+----------+----------------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+------+-------------+-------------+-------+--------------------+-----------+---------+------+-------+----------+----------------------------------------------------+

| 1 | SIMPLE | local_deals | range | latitude,longitude | longitude | 5 | NULL | 30662 | 100.00 | Using index condition; Using where; Using filesort |

+------+-------------+-------------+-------+--------------------+-----------+---------+------+-------+----------+----------------------------------------------------+

这表明经度索引可以更好地将表筛选为30,662行.我在这里想念什么吗？如何获得MySQL以使用所有查询.请注意,该表是InnoDB,并且我正在使用MySQL 5.5.