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;

Answer 1

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.

Answer 2

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.