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

EverSQL Database Performance Knowledge Base

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

Database type:

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):

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;

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `AvailableAddressesV3` ADD INDEX `availableaddresses_idx_postnr_completeaddress` (`postNr`,`completeAddress`);
The optimized query:
SELECT
        DISTINCT DB_M3_Medium.AvailableAddressesV3.completeAddress 
    FROM
        DB_M3_Medium.AvailableAddressesV3 
    WHERE
        DB_M3_Medium.AvailableAddressesV3.postNr = 1050 
    ORDER BY
        DB_M3_Medium.AvailableAddressesV3.completeAddress ASC

Related Articles



* original question posted on StackOverflow here.