Xrhrft

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;

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?

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.

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.