MySQL optimizer won't use my index

Question

With the following query:

SELECT 
  *
FROM 
  geoname 
  LEFT JOIN alternatename
    ON geoname.geonameid = alternatename.geonameid
    AND alternatename.isPreferredName = 1 
    AND alternatename.isoLanguage = 'es'
WHERE 
  geoname.fcode = 'ADM2' 
  AND geoname.country = 'ES'

I get the following execution plan:

+----+-------------+---------------+------+-----------------------------------------+--------------------+---------+----------------------------------+------+-----------------------+
| id | select_type | table         | type | possible_keys                           | key                | key_len | ref                              | rows | Extra                 |
+----+-------------+---------------+------+-----------------------------------------+--------------------+---------+----------------------------------+------+-----------------------+
|  1 | SIMPLE      | geoname       | ref  | fcode_idx,country_idx,fcode_country_idx | fcode_country_idx  | 42      | const,const                      |   51 | Using index condition |
|  1 | SIMPLE      | alternatename | ref  | IDX_8F82EED4E2097D,idx_geoid_lang_pref  | IDX_8F82EED4E2097D | 4       | acme_geonames.geoname.geonameid  |    1 | Using where           |
+----+-------------+---------------+------+-----------------------------------------+--------------------+---------+----------------------------------+------+-----------------------+

The IDX_8F82EED4E2097D is on the geonameid field only, while the idx_geoid_lang_pref index is on the fields:

1. geonameid
2. isPreferredName
3. isoLanguage

Why doesn't MySQL use the idx_geoid_lang_pref index for the alternatename join, which I made specifically for this query?

Update as asked in comments:

the exact version of MySQL

5.6.24

what are the tables sizes?

• geoname: 4.082.489 rows
• alternatename: 2.558.890 rows

How many rows does the query return?

52

How much time does it need to run?

0:00:0.00142580 according to QueryStats

Note: my goal is not to speed up this particular request, only to understand why an index which cover the exact fields of the query is not chosen by the optimizer.

Provide the full SHOW CREATE TABLE statement for each table

CREATE TABLE `geoname` (
  `geonameid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `asciiname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `fclass` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fcode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `country` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cc2` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `admin1` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `admin2` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `admin3` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `admin4` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `elevation` int(11) DEFAULT NULL,
  `gtopo30` int(11) DEFAULT NULL,
  `timezone` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `moddate` date DEFAULT NULL,
  PRIMARY KEY (`geonameid`),
  KEY `fclass_idx` (`fclass`),
  KEY `fcode_idx` (`fcode`),
  KEY `country_idx` (`country`),
  KEY `admin1_idx` (`admin1`),
  KEY `admin2_idx` (`admin2`),
  KEY `fcode_country_idx` (`fcode`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=10346797 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `alternatename` (
  `alternatenameId` int(11) NOT NULL AUTO_INCREMENT,
  `geonameid` int(11) NOT NULL,
  `isoLanguage` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
  `alternateName` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `isPreferredName` tinyint(1) NOT NULL,
  `isShortName` tinyint(1) NOT NULL,
  `isColloquial` tinyint(1) NOT NULL,
  `isHistoric` tinyint(1) NOT NULL,
  PRIMARY KEY (`alternatenameId`),
  KEY `IDX_8F82EED4E2097D` (`geonameid`),
  KEY `idx_geoid_lang_pref` (`geonameid`,`isPreferredName`,`isoLanguage`),
  CONSTRAINT `FK_8F82EED4E2097D` FOREIGN KEY (`geonameid`) REFERENCES `geoname` (`geonameid`)
) ENGINE=InnoDB AUTO_INCREMENT=10842837 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

You do not use the isPreferredName in your query. This must be a/the reason why the index is not used.

Also, the optimizer decides. It is not because you create an index that it will be used. All depends on the statistics for the table. How many rows? How many different unique values? What is the expected percentage of returned rows?

Answer 2

MySQL already can filter down to 1 row by using a different smaller index (only 4 bytes).

|  1 | SIMPLE      | alternatename | ref  | IDX_8F82EED4E2097D,idx_geoid_lang_pref  | IDX_8F82EED4E2097D | 4       | acme_geonames.geoname.geonameid  |    1 | Using where 

So it doesn't need to use the bigger index.

Hope this answers your question.

Answer 3

Rearrange KEY idx_geoid_lang_pref (geonameid,isPreferredName,isoLanguage), to put geonameid at the end.

In SELECT *, how many of the fields of alternatename do you need? If only one, do a subquery instead of a JOIN.

I see no good reason to have alternatenameId. And alternatename.geonameid is unique, correct? Hence, geonameid could be the PRIMARY KEY for that table.

Answer 4

It gains little by using your index, and it takes more memory to load that index due to the additional columns.

Since you SELECT * it still needs all the columns in the alternatename table, so the fact that it can locate the rows faster with your idx_geoid_lang_pref has to be offset by the fact that it still needs to make an extra trip to the clustered index anyway.

So, it is likely choosing the shorter index in order to load more of it into memory at once, find rows that match that one column, then go back to the clustered index to filter out the remaining records, since it needs to go to the clustered anyway in order to fulfill your SELECT * clause.

I'm not an expert in optimizer design or functionality by any means, but this is a logical reason that it has to make this decision. Whether it is right to do so is not something I know.

If you really want to get the most benefit out of indexes, make sure they are covering indexes. That means all the columns in both the WHERE and the SELECT clause need to be in the index, not just the WHERE clause.