Why adding duplicate indexes to MySQL table caused longer query execution time?

Question

Maybe the index wasn't relevant but I am experiencing a strange issue.

This is my select query:

SELECT DISTINCT completeAddress FROM DB_M3_Medium.AvailableAddressesV3 where postNr = 1050 ORDER BY completeAddress ASC;

My indexes:

create index postNrAndAddress_idx on DB_M3_Medium.AvailableAddressesV3 (completeAddress);
create index postNr_idx on DB_M3_Medium.AvailableAddressesV3 (completeAddress);
create index completeAddress_idx on DB_M3_Medium.AvailableAddressesV3 (completeAddress);

And besides that I've got a PK on an autoincrement id (idIndex).

The execution time of the select query before any of the manually created indexes were present was 2.4s.

Then I have created indexes (one by one):

1st index - select statement execution time - 2.1s
2nd index - select statement execution time - 2.8s
3rd index - select statement execution time - 12.7s

What's just happened?

EDIT:

Thank you guys for your comments. My explain statement result:

+----+-------------+----------------------+-------+-----------------------------------------------------+---------------------+---------+-----+---------+-------------+
| id | select_type |        table         | type  |                    possible_keys                    |         key         | key_len | ref |  rows   |    Extra    |
+----+-------------+----------------------+-------+-----------------------------------------------------+---------------------+---------+-----+---------+-------------+
|  1 | SIMPLE      | AvailableAddressesV3 | index | postNrAndAddress_idx,postNr_idx,completeAddress_idx | completeAddress_idx |     363 |     | 3526406 | Using where |
+----+-------------+----------------------+-------+-----------------------------------------------------+---------------------+---------+-----+---------+-------------+

Table structure:

+------------------+--------------+------+-----+---------+----------------+
|      Field       |     Type     | Null | Key | Default |     Extra      |
+------------------+--------------+------+-----+---------+----------------+
| vej_Navn         | varchar(70)  | YES  |     |         |                |
| husNr            | varchar(20)  | YES  |     |         |                |
| husbogstav       | varchar(50)  | YES  |     |         |                |
| etage            | varchar(30)  | YES  |     |         |                |
| side_DoerNr      | varchar(20)  | YES  |     |         |                |
| stedNavn         | varchar(50)  | YES  |     |         |                |
| postNr           | varchar(15)  | YES  | MUL |         |                |
| postDistrikt     | varchar(50)  | YES  |     |         |                |
| lev_Adresse_UUID | varchar(50)  | YES  |     |         |                |
| fiberstatus      | varchar(15)  | YES  |     |         |                |
| kommune_nr       | varchar(35)  | YES  |     |         |                |
| vej_Kode         | varchar(35)  | YES  |     |         |                |
| completeAddress  | varchar(120) | YES  | MUL |         |                |
| randomSalt       | varchar(5)   | YES  |     |         |                |
| id               | int(11)      | NO   | PRI |         | auto_increment |
+------------------+--------------+------+-----+---------+----------------+

Create table query:

  CREATE TABLE `AvailableAddressesV3` (
  `vej_Navn` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  `husNr` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `husbogstav` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `etage` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `side_DoerNr` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `stedNavn` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `postNr` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
  `postDistrikt` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lev_Adresse_UUID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fiberstatus` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `kommune_nr` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL,
  `vej_Kode` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL,
  `completeAddress` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  `randomSalt` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `idIndex` (`id`),
  KEY `postNrAndAddress_idx` (`postNr`,`completeAddress`),
  KEY `postNr_idx` (`postNr`),
  KEY `completeAddress_idx` (`completeAddress`)
) ENGINE=InnoDB AUTO_INCREMENT=3552718 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Your first stop for all of your why-is-my-query-doing-this questions: http://dev.mysql.com/doc/refman/5.7/en/explain.html — CBroe, Sep 16 '16 at 08:25
I guess it's a copy&paste error, but you added three times the same index (always on `completeAddress`). Additionally to showing the EXPLAIN statement results as CBroe suggest, post your schema as well. — Paul, Sep 16 '16 at 08:29
Note that every index may speed up SELECT queries but will slow down UPDATE/INSERT/DELETE because this index must be updated. It is easy to "guess" to add all possible indexes and hope that everything will run faster. — i486, Sep 16 '16 at 08:46
I am not having issues with UPDATE/INSERT/DELETE and yes I am aware of it. I was testing and trying to improve performance of the SELECT. And no it hasn't improved the speed by adding the indexes, it has done the exact opposite. :( — Ondrej Tokar, Sep 16 '16 at 08:49
`postNr_idx` is not necessary. It is covered via left-most chunk in the composite key above it — Drew, Sep 16 '16 at 08:54
@Drew It can. But put 10 unnecessary indexes on big table and then look how the speed slows down. Not for seconds, but a query may take 0.3 seconds for example instead of 0.001 s. For intensive work this is valuable. And for million row table the time can be not 0.3sec but 3sec. — i486, Sep 16 '16 at 08:54
my point is that it is an inaccurate comment. I showed a guy how a 30 second table scan could be reduced to 200ms — Drew, Sep 16 '16 at 08:55
@Drew This is comments section and a comment is not necessary to be answer. I only noted that many indexes may have side effects. — i486, Sep 16 '16 at 08:57
@OndrejTokar Just to clarify, the measured times in your question are *not* including table creation and inserts, right? — André Laszlo, Sep 16 '16 at 08:58

André Laszlo · Answer 1 · 2016-09-16T09:03:35.550

1

Based on your EXPLAIN output, the query is using the completeAddress_idx, probably because of the sort/distinct, but I'm guessing there are very few rows with the postNr = 1050 (in Copenhagen, right?) so it should be more efficient to use postNr_idx or postNrAndAddress_idx (sorting/distinct on a couple of hundred rows should be almost instant). Something is making the query execution planner miss the optimal query.

I have never tried this myself, but you could try the ANALYZE TABLE statement which updates table statistics, for example key cardinality, that could change how the optimizer works.

Either that, or I'm missing something simple - which seems likely :)

Edit

While debugging, it can be useful to force MySQL to use a specific index. Try the FORCE/USE INDEX hint.

edited Sep 16 '16 at 09:03

answered Sep 16 '16 at 08:54

André Laszlo

15,169
3
63
81

Thanks a lot @AndreLaszlo I have tried it with `postNr` index only and the execution time is now 2.6s which is still higher than not having an index at all. Soo strange. – Ondrej Tokar Sep 16 '16 at 09:00
Interesting, what is the output of EXPLAIN for that query? – André Laszlo Sep 16 '16 at 09:01
Hmm but after executing EXPLAIN now when I have only `postNr` index, it says it's a possible key, but not a chosen key. The chosen key is null. How can I force it to use that key? – Ondrej Tokar Sep 16 '16 at 09:01
Answer updated. Also, how are you measuring the query time. It's only for the SELECT itself? Not including CREATE and INSERTs right? – André Laszlo Sep 16 '16 at 09:05
Yes the time is measured only for the select. – Ondrej Tokar Sep 16 '16 at 09:10
I have tried this: `SELECT DISTINCT completeAddress FROM DB_M3_Medium.AvailableAddressesV3 USE INDEX (postNr_idx) where postNr = 1050 ORDER BY completeAddress ASC;` same with FORCE and the EXPLAIN returns the same thing and execution time is still the same. Key column is still null. – Ondrej Tokar Sep 16 '16 at 09:13
What about FORCE INDEX? Otherwise I'm out of ideas. It seems like you are using Amazon RDS, so maybe try to create a minimal example schema with the same issue (not using the index even after hinting) and talk to their support? Please update the question if you find a solution :) – André Laszlo Sep 16 '16 at 09:26
I've tried FORCE too as I mentioned in my above comment. I am also out of ideas, that's why I posted the question here :). Anyway thanks a lot for trying to help out. I am overspending on this project already ... won't be able to investigate further. – Ondrej Tokar Sep 16 '16 at 09:27
1

Ah sorry. Read too quickly. Too bad! – André Laszlo Sep 16 '16 at 09:29

score 1 · Answer 2 · edited May 23 '17 at 12:08

I would never expect that this could be an issue or at least that I would get notified by WorkBench or JDBC with an error or at least a warning.

My select query should look like this:

SELECT DISTINCT completeAddress FROM DB_M3_Medium.AvailableAddressesV3 where postNr = '4000' ORDER BY completeAddress ASC;

The difference is the datatype of the postNr. Before I didn't have it wrapped in '.

That improved the select crazily, and then when I removed ORDER BY the execution time dropped down to 0.07s.

So basically what was happening, was that the SELECT query wasn't using any index because none of the indexes was suitable. When I did the EXPLAIN I was receiving NULL my Key column. I was trying to FORCE it, but it made no difference.

Then I have discovered this: Why isn't MySQL using any of these possible keys?

Where in the second answer he has mentioned it.

I was just experimenting and came back to answer the exact same thing! :) Nice! — André Laszlo, Sep 16 '16 at 11:05

Why adding duplicate indexes to MySQL table caused longer query execution time?

2 Answers2