MySQL optimizer won't use my indexUnexplained InnoDB timeoutsAre two indexes needed?Identical query, tables,...
Should we avoid writing fiction about historical events without extensive research?
Which sins are beyond punishment?
The need of reserving one's ability in job interviews
How to evaluate the limit where something is raised to a power of x?
Is it possible to make a clamp function shorter than a ternary in JS?
Why do members of Congress in committee hearings ask witnesses the same question multiple times?
Why did John Williams use a march to symbolise Indiana Jones?
Is there a full canon version of Tyrion's jackass/honeycomb joke?
Can I become debt free or should I file for bankruptcy? How do I manage my debt and finances?
Real life puzzle: Unknown alphabet or shorthand
In iTunes 12 on macOS, how can I reset the skip count of a song?
How to lift/raise/repair a segment of concrete slab?
What are the issues with an additional (limited) concentration slot instead of Bladesong?
Non-Italian European mafias in USA?
VAT refund for a conference ticket in Sweden
How can I be pwned if I'm not registered on the compromised site?
Canadian citizen, on US no-fly list. What can I do in order to be allowed on flights which go through US airspace?
Source for Cremation Specifically Not Jewish
Is there a frame of reference in which I was born before I was conceived?
Reason why dimensional travelling would be restricted
What are all the squawk codes?
Borrowing Characters
What could trigger powerful quakes on icy world?
What type of investment is best suited for a 1-year investment on a down payment?
MySQL optimizer won't use my index
Unexplained InnoDB timeoutsAre two indexes needed?Identical query, tables, but different EXPLAIN and performanceAdding index to large mysql tablesSimple query is slow on 4M-rows tableOptimizing a simple query on a large tableNeed help improving sql query performanceMySQL query taking too longselect MAX() from MySQL view (2x INNER JOIN) is slowslow queries on indexed columns (large datasets)
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:
geonameidisPreferredNameisoLanguage
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
asked Nov 17 '15 at 11:00
marcvmarcv
1387
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 4 more comments
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:
geonameidisPreferredNameisoLanguage
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
asked Nov 17 '15 at 11:00
marcvmarcv
1387
bumped to the homepage by Community♦ 2 mins 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
|
show 4 more comments
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:
geonameidisPreferredNameisoLanguage
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
asked Nov 17 '15 at 11:00
marcvmarcv
1387
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:
geonameidisPreferredNameisoLanguage
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
mysql join execution-plan explain
asked Nov 17 '15 at 11:00
marcvmarcv
1387
asked Nov 17 '15 at 11:00
marcvmarcv
1387
edited Nov 17 '15 at 14:58
marcv
asked Nov 17 '15 at 11:00
marcvmarcv
1387
asked Nov 17 '15 at 11:00
marcvmarcv
1387
asked Nov 17 '15 at 11:00
marcvmarcv
1387
1387
bumped to the homepage by Community♦ 2 mins 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♦ 2 mins 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
|
show 4 more comments
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
|
show 4 more comments
3 Answers
3
active
oldest
votes
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?
answered Nov 17 '15 at 11:08
MarcoMarco
3,74231724
Sorry, I do use theisPreferredNamefield in my query, my bad, I updated the code.
– marcv
Nov 17 '15 at 11:14
add a comment |
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.
answered Nov 17 '15 at 17:26
Károly NagyKároly Nagy
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
add a comment |
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.
answered Dec 3 '15 at 2:44
Rick JamesRick James
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
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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?
answered Nov 17 '15 at 11:08
MarcoMarco
3,74231724
Sorry, I do use theisPreferredNamefield in my query, my bad, I updated the code.
– marcv
Nov 17 '15 at 11:14
add a comment |
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?
answered Nov 17 '15 at 11:08
MarcoMarco
3,74231724
Sorry, I do use theisPreferredNamefield in my query, my bad, I updated the code.
– marcv
Nov 17 '15 at 11:14
add a comment |
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?
answered Nov 17 '15 at 11:08
MarcoMarco
3,74231724
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?
answered Nov 17 '15 at 11:08
MarcoMarco
3,74231724
answered Nov 17 '15 at 11:08
MarcoMarco
3,74231724
answered Nov 17 '15 at 11:08
MarcoMarco
3,74231724
answered Nov 17 '15 at 11:08
MarcoMarco
3,74231724
3,74231724
Sorry, I do use theisPreferredNamefield in my query, my bad, I updated the code.
– marcv
Nov 17 '15 at 11:14
add a comment |
Sorry, I do use theisPreferredNamefield 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
add a comment |
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.
answered Nov 17 '15 at 17:26
Károly NagyKároly Nagy
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
add a comment |
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.
answered Nov 17 '15 at 17:26
Károly NagyKároly Nagy
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
add a comment |
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.
answered Nov 17 '15 at 17:26
Károly NagyKároly Nagy
2,5201611
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.
answered Nov 17 '15 at 17:26
Károly NagyKároly Nagy
2,5201611
answered Nov 17 '15 at 17:26
Károly NagyKároly Nagy
2,5201611
answered Nov 17 '15 at 17:26
Károly NagyKároly Nagy
2,5201611
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
add a comment |
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
add a comment |
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.
answered Dec 3 '15 at 2:44
Rick JamesRick James
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
add a comment |
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.
answered Dec 3 '15 at 2:44
Rick JamesRick James
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
add a comment |
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.
answered Dec 3 '15 at 2:44
Rick JamesRick James
43.1k22259
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.
answered Dec 3 '15 at 2:44
Rick JamesRick James
43.1k22259
answered Dec 3 '15 at 2:44
Rick JamesRick James
43.1k22259
answered Dec 3 '15 at 2:44
Rick JamesRick James
43.1k22259
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
add a comment |
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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