MySQL optimizer won't use my index












0















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;









share|improve this question
















bumped to the homepage by Community 1 min ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.











  • 1





    I sure, correct answer for question - WHY? no body excluding few guys from Oracle/Percona/MariaDB who wrote query optimisation can not answer :) different versions of MySQL have different query optimisation algorythms also. if You want test it with other index - test it with FORCE index hint

    – a_vlad
    Nov 17 '15 at 11:32













  • I would suggest an index with different order: (isPreferredName, isoLanguage, geonameid)

    – ypercubeᵀᴹ
    Nov 17 '15 at 11:34











  • But you haven't provided with the exact version of MySQL (as @a_vlad pointed). And what are the tables sizes? How many rows does the query return? How much time does it need to run?

    – ypercubeᵀᴹ
    Nov 17 '15 at 11:37













  • Thanks for your comments, I updated my question with the data you asked.

    – marcv
    Nov 17 '15 at 11:55






  • 1





    I wonder why there is a question there. The query returns the answer (52 rows) in about a millisecond. Why would anyone care what indexes it uses?

    – ypercubeᵀᴹ
    Nov 17 '15 at 12:05
















0















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;









share|improve this question
















bumped to the homepage by Community 1 min ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.











  • 1





    I sure, correct answer for question - WHY? no body excluding few guys from Oracle/Percona/MariaDB who wrote query optimisation can not answer :) different versions of MySQL have different query optimisation algorythms also. if You want test it with other index - test it with FORCE index hint

    – a_vlad
    Nov 17 '15 at 11:32













  • I would suggest an index with different order: (isPreferredName, isoLanguage, geonameid)

    – ypercubeᵀᴹ
    Nov 17 '15 at 11:34











  • But you haven't provided with the exact version of MySQL (as @a_vlad pointed). And what are the tables sizes? How many rows does the query return? How much time does it need to run?

    – ypercubeᵀᴹ
    Nov 17 '15 at 11:37













  • Thanks for your comments, I updated my question with the data you asked.

    – marcv
    Nov 17 '15 at 11:55






  • 1





    I wonder why there is a question there. The query returns the answer (52 rows) in about a millisecond. Why would anyone care what indexes it uses?

    – ypercubeᵀᴹ
    Nov 17 '15 at 12:05














0












0








0








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;









share|improve this 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;






mysql join execution-plan explain






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 17 '15 at 14:58







marcv

















asked Nov 17 '15 at 11:00









marcvmarcv

1387




1387





bumped to the homepage by Community 1 min ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 1 min ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.










  • 1





    I sure, correct answer for question - WHY? no body excluding few guys from Oracle/Percona/MariaDB who wrote query optimisation can not answer :) different versions of MySQL have different query optimisation algorythms also. if You want test it with other index - test it with FORCE index hint

    – a_vlad
    Nov 17 '15 at 11:32













  • I would suggest an index with different order: (isPreferredName, isoLanguage, geonameid)

    – ypercubeᵀᴹ
    Nov 17 '15 at 11:34











  • But you haven't provided with the exact version of MySQL (as @a_vlad pointed). And what are the tables sizes? How many rows does the query return? How much time does it need to run?

    – ypercubeᵀᴹ
    Nov 17 '15 at 11:37













  • Thanks for your comments, I updated my question with the data you asked.

    – marcv
    Nov 17 '15 at 11:55






  • 1





    I wonder why there is a question there. The query returns the answer (52 rows) in about a millisecond. Why would anyone care what indexes it uses?

    – ypercubeᵀᴹ
    Nov 17 '15 at 12:05














  • 1





    I sure, correct answer for question - WHY? no body excluding few guys from Oracle/Percona/MariaDB who wrote query optimisation can not answer :) different versions of MySQL have different query optimisation algorythms also. if You want test it with other index - test it with FORCE index hint

    – a_vlad
    Nov 17 '15 at 11:32













  • I would suggest an index with different order: (isPreferredName, isoLanguage, geonameid)

    – ypercubeᵀᴹ
    Nov 17 '15 at 11:34











  • But you haven't provided with the exact version of MySQL (as @a_vlad pointed). And what are the tables sizes? How many rows does the query return? How much time does it need to run?

    – ypercubeᵀᴹ
    Nov 17 '15 at 11:37













  • Thanks for your comments, I updated my question with the data you asked.

    – marcv
    Nov 17 '15 at 11:55






  • 1





    I wonder why there is a question there. The query returns the answer (52 rows) in about a millisecond. Why would anyone care what indexes it uses?

    – ypercubeᵀᴹ
    Nov 17 '15 at 12:05








1




1





I sure, correct answer for question - WHY? no body excluding few guys from Oracle/Percona/MariaDB who wrote query optimisation can not answer :) different versions of MySQL have different query optimisation algorythms also. if You want test it with other index - test it with FORCE index hint

– a_vlad
Nov 17 '15 at 11:32







I sure, correct answer for question - WHY? no body excluding few guys from Oracle/Percona/MariaDB who wrote query optimisation can not answer :) different versions of MySQL have different query optimisation algorythms also. if You want test it with other index - test it with FORCE index hint

– a_vlad
Nov 17 '15 at 11:32















I would suggest an index with different order: (isPreferredName, isoLanguage, geonameid)

– ypercubeᵀᴹ
Nov 17 '15 at 11:34





I would suggest an index with different order: (isPreferredName, isoLanguage, geonameid)

– ypercubeᵀᴹ
Nov 17 '15 at 11:34













But you haven't provided with the exact version of MySQL (as @a_vlad pointed). And what are the tables sizes? How many rows does the query return? How much time does it need to run?

– ypercubeᵀᴹ
Nov 17 '15 at 11:37







But you haven't provided with the exact version of MySQL (as @a_vlad pointed). And what are the tables sizes? How many rows does the query return? How much time does it need to run?

– ypercubeᵀᴹ
Nov 17 '15 at 11:37















Thanks for your comments, I updated my question with the data you asked.

– marcv
Nov 17 '15 at 11:55





Thanks for your comments, I updated my question with the data you asked.

– marcv
Nov 17 '15 at 11:55




1




1





I wonder why there is a question there. The query returns the answer (52 rows) in about a millisecond. Why would anyone care what indexes it uses?

– ypercubeᵀᴹ
Nov 17 '15 at 12:05





I wonder why there is a question there. The query returns the answer (52 rows) in about a millisecond. Why would anyone care what indexes it uses?

– ypercubeᵀᴹ
Nov 17 '15 at 12:05










3 Answers
3






active

oldest

votes


















0














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?






share|improve this answer
























  • Sorry, I do use the isPreferredName field in my query, my bad, I updated the code.

    – marcv
    Nov 17 '15 at 11:14



















0














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.






share|improve this answer
























  • I don't think this point helps any -- (1) drill down secondary BTree via geonameid, (2) drill down primary BTree to find the other 2 fields and check them. With the bigger index, "(2)" is avoided for the cases where they fail.

    – Rick James
    Dec 3 '15 at 2:47



















0














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.






share|improve this answer
























  • My goal is not to correct the query to make it more efficient. What I want is understand the decision of the optimizer. And alternatename.geonameid is not unique, nope, as one geoname can have multiple alternatenames. It is a very badly designed schema but this is another topic.

    – marcv
    Dec 3 '15 at 22:05













  • Well, I don't understand why it picked the shorter index. It seems like a 'bug' in the optimizer. bugs.mysql.com might get to the optimizer guys and get the real answer. They are currently keen on "getting it right" in 5.7.

    – Rick James
    Dec 3 '15 at 23:28











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f121322%2fmysql-optimizer-wont-use-my-index%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























3 Answers
3






active

oldest

votes








3 Answers
3






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














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?






share|improve this answer
























  • Sorry, I do use the isPreferredName field in my query, my bad, I updated the code.

    – marcv
    Nov 17 '15 at 11:14
















0














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?






share|improve this answer
























  • Sorry, I do use the isPreferredName field in my query, my bad, I updated the code.

    – marcv
    Nov 17 '15 at 11:14














0












0








0







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?






share|improve this answer













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?







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 17 '15 at 11:08









MarcoMarco

3,74231724




3,74231724













  • Sorry, I do use the isPreferredName field in my query, my bad, I updated the code.

    – marcv
    Nov 17 '15 at 11:14



















  • Sorry, I do use the isPreferredName field in my query, my bad, I updated the code.

    – marcv
    Nov 17 '15 at 11:14

















Sorry, I do use the isPreferredName field in my query, my bad, I updated the code.

– marcv
Nov 17 '15 at 11:14





Sorry, I do use the isPreferredName field in my query, my bad, I updated the code.

– marcv
Nov 17 '15 at 11:14













0














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.






share|improve this answer
























  • I don't think this point helps any -- (1) drill down secondary BTree via geonameid, (2) drill down primary BTree to find the other 2 fields and check them. With the bigger index, "(2)" is avoided for the cases where they fail.

    – Rick James
    Dec 3 '15 at 2:47
















0














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.






share|improve this answer
























  • I don't think this point helps any -- (1) drill down secondary BTree via geonameid, (2) drill down primary BTree to find the other 2 fields and check them. With the bigger index, "(2)" is avoided for the cases where they fail.

    – Rick James
    Dec 3 '15 at 2:47














0












0








0







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.






share|improve this answer













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.







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 17 '15 at 17:26









Károly NagyKároly Nagy

2,5201611




2,5201611













  • I don't think this point helps any -- (1) drill down secondary BTree via geonameid, (2) drill down primary BTree to find the other 2 fields and check them. With the bigger index, "(2)" is avoided for the cases where they fail.

    – Rick James
    Dec 3 '15 at 2:47



















  • I don't think this point helps any -- (1) drill down secondary BTree via geonameid, (2) drill down primary BTree to find the other 2 fields and check them. With the bigger index, "(2)" is avoided for the cases where they fail.

    – Rick James
    Dec 3 '15 at 2:47

















I don't think this point helps any -- (1) drill down secondary BTree via geonameid, (2) drill down primary BTree to find the other 2 fields and check them. With the bigger index, "(2)" is avoided for the cases where they fail.

– Rick James
Dec 3 '15 at 2:47





I don't think this point helps any -- (1) drill down secondary BTree via geonameid, (2) drill down primary BTree to find the other 2 fields and check them. With the bigger index, "(2)" is avoided for the cases where they fail.

– Rick James
Dec 3 '15 at 2:47











0














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.






share|improve this answer
























  • My goal is not to correct the query to make it more efficient. What I want is understand the decision of the optimizer. And alternatename.geonameid is not unique, nope, as one geoname can have multiple alternatenames. It is a very badly designed schema but this is another topic.

    – marcv
    Dec 3 '15 at 22:05













  • Well, I don't understand why it picked the shorter index. It seems like a 'bug' in the optimizer. bugs.mysql.com might get to the optimizer guys and get the real answer. They are currently keen on "getting it right" in 5.7.

    – Rick James
    Dec 3 '15 at 23:28
















0














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.






share|improve this answer
























  • My goal is not to correct the query to make it more efficient. What I want is understand the decision of the optimizer. And alternatename.geonameid is not unique, nope, as one geoname can have multiple alternatenames. It is a very badly designed schema but this is another topic.

    – marcv
    Dec 3 '15 at 22:05













  • Well, I don't understand why it picked the shorter index. It seems like a 'bug' in the optimizer. bugs.mysql.com might get to the optimizer guys and get the real answer. They are currently keen on "getting it right" in 5.7.

    – Rick James
    Dec 3 '15 at 23:28














0












0








0







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.






share|improve this answer













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.







share|improve this answer












share|improve this answer



share|improve this answer










answered Dec 3 '15 at 2:44









Rick JamesRick James

43.1k22259




43.1k22259













  • My goal is not to correct the query to make it more efficient. What I want is understand the decision of the optimizer. And alternatename.geonameid is not unique, nope, as one geoname can have multiple alternatenames. It is a very badly designed schema but this is another topic.

    – marcv
    Dec 3 '15 at 22:05













  • Well, I don't understand why it picked the shorter index. It seems like a 'bug' in the optimizer. bugs.mysql.com might get to the optimizer guys and get the real answer. They are currently keen on "getting it right" in 5.7.

    – Rick James
    Dec 3 '15 at 23:28



















  • My goal is not to correct the query to make it more efficient. What I want is understand the decision of the optimizer. And alternatename.geonameid is not unique, nope, as one geoname can have multiple alternatenames. It is a very badly designed schema but this is another topic.

    – marcv
    Dec 3 '15 at 22:05













  • Well, I don't understand why it picked the shorter index. It seems like a 'bug' in the optimizer. bugs.mysql.com might get to the optimizer guys and get the real answer. They are currently keen on "getting it right" in 5.7.

    – Rick James
    Dec 3 '15 at 23:28

















My goal is not to correct the query to make it more efficient. What I want is understand the decision of the optimizer. And alternatename.geonameid is not unique, nope, as one geoname can have multiple alternatenames. It is a very badly designed schema but this is another topic.

– marcv
Dec 3 '15 at 22:05







My goal is not to correct the query to make it more efficient. What I want is understand the decision of the optimizer. And alternatename.geonameid is not unique, nope, as one geoname can have multiple alternatenames. It is a very badly designed schema but this is another topic.

– marcv
Dec 3 '15 at 22:05















Well, I don't understand why it picked the shorter index. It seems like a 'bug' in the optimizer. bugs.mysql.com might get to the optimizer guys and get the real answer. They are currently keen on "getting it right" in 5.7.

– Rick James
Dec 3 '15 at 23:28





Well, I don't understand why it picked the shorter index. It seems like a 'bug' in the optimizer. bugs.mysql.com might get to the optimizer guys and get the real answer. They are currently keen on "getting it right" in 5.7.

– Rick James
Dec 3 '15 at 23:28


















draft saved

draft discarded




















































Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f121322%2fmysql-optimizer-wont-use-my-index%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Liste der Baudenkmale in Friedland (Mecklenburg)

Single-Malt-Whisky

Czorneboh