[MDEV-27152] MariaDB prefers table scan over unique/primary keys if index is overspecified Created: 2021-12-01  Updated: 2022-07-26

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.12
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Marc Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File query_with_index_as_json.txt     Text File query_without_index_as_json.txt    

 Description   

With explain I can see some queries don't use the primary/unique key. In case I create an index on the same column, it gets used right away. I was unable to reproduce this on simple tables but I can show some output:

The query:

explain SELECT *
 
 FROM rdLogin l LEFT JOIN
 
qmSchichten s ON (s.ID=l.SchichtID AND s.OrganisationID IN(18)) LEFT JOIN
 
qmSchichtPlatz p ON s.Platz=p.ID LEFT JOIN
 
rdFunkruf rf ON (l.Funkruf=rf.ID AND rf.OrganisationID IN(18)) LEFT JOIN
 
tblWebUser u ON u.ID=l.UID LEFT JOIN
 
tblWebUser d ON d.ID=l.durch LEFT JOIN
 
rdFahrzeug f ON l.Fahrzeug=f.ID WHERE 
l.OrganisationID IN(18) AND l.WacheID=170 
ORDER BY l.Datum DESC LIMIT 0,50

produces:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE l ref OrganisationID_Datum,WacheID_Datum WacheID_Datum 3 const 2634 Using where
1 SIMPLE s eq_ref PRIMARY,OrganisationID_von PRIMARY 4 l.SchichtID 1 Using where
1 SIMPLE p eq_ref PRIMARY PRIMARY 3 s.Platz 1 Using where
1 SIMPLE rf eq_ref PRIMARY,Wache PRIMARY 2 l.Funkruf 1 Using where
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 l.UID 1  
1 SIMPLE d eq_ref PRIMARY PRIMARY 4 l.durch 1  
1 SIMPLE f ALL PRIMARY NULL NULL NULL 4147 Using where; Using join buffer (flat, BNL join)

After adding a key (which is the same as the simple primary key):

alter table rdFahrzeug add KEY `ID` (`ID`);

I get this:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE l ref OrganisationID_Datum,WacheID_Datum WacheID_Datum 3 const 2634 Using where
1 SIMPLE s eq_ref PRIMARY,OrganisationID_von PRIMARY 4 l.SchichtID 1 Using where
1 SIMPLE p eq_ref PRIMARY PRIMARY 3 s.Platz 1 Using where
1 SIMPLE rf eq_ref PRIMARY,Wache PRIMARY 2 l.Funkruf 1 Using where
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 l.UID 1  
1 SIMPLE d eq_ref PRIMARY PRIMARY 4 l.durch 1  
1 SIMPLE f ref PRIMARY,ID ID 2 l.Fahrzeug 1 Using where

Afaik this bug was introduced in MariaDB 10.4/10.5; but today I realized what happens here.

If you need more info, just ask.

 Comments   
Comment by Marc [ 2021-12-06 ]

The problem occurs quite often. It looks like this comes, if tables are full joined and the order of tables is resorted by the optimizer. But this is still a guess.
But having tons of slow-queries and long query times, even if the correct indexes are set is quite annyoing.

Comment by Marc [ 2022-01-24 ]

Anything needed to reproduce?
I have tons of slow queries due to this bug.

Comment by Marc [ 2022-02-17 ]

I can't reproduce the issue above, but the optimizer still makes some confusing decissions (I know it is a hard task):
if you have 2 tables 1:1 joined and have a superflu condition to the primary index, the optimizer decides not to use the primary index:

CREATE TABLE `A` (
 
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `super` mediumint(8) unsigned NOT NULL,
 
  PRIMARY KEY (`ID`),
 
  KEY `s` (`super`)
 
)
 
CREATE TABLE `B` (
 
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `super` mediumint(8) unsigned NOT NULL,
 
  PRIMARY KEY (`ID`),
 
  KEY `s` (`super`)
 
)
 
 
 
SELECT * 
FROM
 
  A JOIN B ON (A.ID=B.ID AND b.super IN (2))
 
WHERE 
  a.super IN (2)

The superflu condition was added to help the index in case of table reordering (first select is on B instead of A)

Comment by Marc [ 2022-07-26 ]

I've got another example to show where duplication of a unique index shows better performance:

Original query:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY g ref ClusterID,OrganisationID_Status ClusterID 2 const 1705 100.00  
1 PRIMARY rf eq_ref PRIMARY,Wache PRIMARY 2 func 1 1.81 Using where
1 PRIMARY f eq_ref PRIMARY,Status PRIMARY 2 func 1 1.74 Using where
1 PRIMARY w eq_ref PRIMARY,Ressource,Shop,Strassensperrung PRIMARY 2 func 1 1.33 Using where
1 PRIMARY cv ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.g.ClusterID 1 100.00  
1 PRIMARY l ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.g.Lieferant 1 100.00  
1 PRIMARY anmKt ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.g.checkID 1 100.00  
1 PRIMARY erstIn ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.g.initialeFkt 1 100.00  
1 PRIMARY b ALL PRIMARY,Hersteller NULL NULL NULL 5482 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t ref PRIMARY,OrganisationID,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.b.GeraeteTyp 1 1.59 Using where
1 PRIMARY h ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.b.Hersteller 1 100.00  
14 DEPENDENT SUBQUERY rdMPGGeraetZubehoerValues ref Bezeichnung Bezeichnung 2 const 6 100.00 Using where
13 DEPENDENT SUBQUERY rdMPGWartung ref Geraet,PersonalID Geraet 4 qmsystems.g.ID 4 27.97 Using where
12 DEPENDENT SUBQUERY rdMPGWartung ref Geraet,PersonalID Geraet 4 qmsystems.g.ID 4 27.97 Using where
11 DEPENDENT SUBQUERY rdMPGWartung ref Geraet,PersonalID Geraet 4 qmsystems.g.ID 4 27.97 Using where
10 DEPENDENT SUBQUERY rdMPGWartung ref Geraet,PersonalID Geraet 4 qmsystems.g.ID 4 27.97 Using where
9 DEPENDENT SUBQUERY rdWache eq_ref PRIMARY PRIMARY 2 qmsystems.g.wartungsWerkstatt 1 100.00 Using index condition
8 DEPENDENT SUBQUERY rdWache eq_ref PRIMARY PRIMARY 2 qmsystems.g.werkstatt 1 100.00 Using index condition
7 DEPENDENT SUBQUERY rf eq_ref PRIMARY PRIMARY 2 qmsystems.g.Funkruf 1 100.00 Using index condition
7 DEPENDENT SUBQUERY f eq_ref PRIMARY PRIMARY 2 func 1 100.00 Using index condition
6 DEPENDENT SUBQUERY rdWache eq_ref PRIMARY PRIMARY 2 qmsystems.g.stammWerkstatt 1 100.00 Using index condition
5 DEPENDENT SUBQUERY f eq_ref PRIMARY PRIMARY 2 qmsystems.g.FesteinbauFID 1 100.00 Using index condition
5 DEPENDENT SUBQUERY rf eq_ref Fahrzeug Fahrzeug 4 qmsystems.f.ID 1 100.00 Using where
4 DEPENDENT SUBQUERY f eq_ref PRIMARY PRIMARY 2 qmsystems.g.StammFesteinbauFID 1 100.00 Using index condition
4 DEPENDENT SUBQUERY rf eq_ref Stammfahrzeug Stammfahrzeug 4 qmsystems.f.ID 1 100.00 Using where
3 DEPENDENT SUBQUERY rf eq_ref PRIMARY PRIMARY 2 qmsystems.g.StammFunkrufID 1 100.00 Using index condition
3 DEPENDENT SUBQUERY f eq_ref PRIMARY PRIMARY 2 func 1 100.00 Using index condition
2 DEPENDENT SUBQUERY tblWebUser eq_ref PRIMARY PRIMARY 4 qmsystems.g.delID 1 100.00  

Adding index DROP_IX_ID to table rdMPGBezeichnung:

table rdMPGBezeichnung:
 
CREATE TABLE `rdMPGBezeichnung` (
 
  `ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 
  `OrganisationID` smallint(5) unsigned NOT NULL,
 
  `Hersteller` mediumint(8) unsigned NOT NULL,
 
  `GeraeteTyp` mediumint(8) unsigned NOT NULL,
 
  `Name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 
  `UMDNS` smallint(8) unsigned NOT NULL DEFAULT 0,
 
  `Einweisung` enum('nein','einweisungspflichtig','unterweisungspflichtig') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'nein',
 
  `WiederholungsEinweisung` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT 'in Jahren',
 
  `DeactivateWarnMails` date DEFAULT NULL,
 
  `Klasse` enum('keine Klasse','Klasse I','Klasse II a','Klasse II b','Klasse III','Gruppe 1','Gruppe 3','Gruppe 4') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'keine Klasse',
 
  `Betriebsart` enum('aktiv','nicht aktiv') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'nicht aktiv',
 
  PRIMARY KEY (`ID`),
 
  UNIQUE KEY `Hersteller` (`Hersteller`,`GeraeteTyp`,`Name`),
 
  KEY `WiederholungsEinweisung_OrganisationID` (`WiederholungsEinweisung`,`OrganisationID`),
 
  KEY `OrganisationID` (`OrganisationID`),
 
  KEY `DROP_IX_ID` (`ID`),
 
  KEY `OrganisationID_Einweisung` (`OrganisationID`,`Einweisung`)
 
) ENGINE=Aria AUTO_INCREMENT=5866 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PACK_KEYS=1 CHECKSUM=1 PAGE_CHECKSUM=1 ROW_FORMAT=PAGE TRANSACTIONAL=0

As you can see DROP_IX_ID is the same as the primary key, just non unique.
Now the same query:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY g ref ClusterID,OrganisationID_Status ClusterID 2 const 1705 100.00  
1 PRIMARY rf eq_ref PRIMARY,Wache PRIMARY 2 func 1 1.81 Using where
1 PRIMARY f eq_ref PRIMARY,Status PRIMARY 2 func 1 1.74 Using where
1 PRIMARY w eq_ref PRIMARY,Ressource,Shop,Strassensperrung PRIMARY 2 func 1 1.33 Using where
1 PRIMARY cv ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.g.ClusterID 1 100.00  
1 PRIMARY b ref PRIMARY,Hersteller,DROP_IX_ID DROP_IX_ID 3 qmsystems.g.BezeichnungID 1 100.00  
1 PRIMARY t ref PRIMARY,OrganisationID,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.b.GeraeteTyp 1 1.59 Using where
1 PRIMARY h ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.b.Hersteller 1 100.00  
1 PRIMARY l ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.g.Lieferant 1 100.00  
1 PRIMARY anmKt ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.g.checkID 1 100.00  
1 PRIMARY erstIn ref PRIMARY,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.g.initialeFkt 1 100.00  
14 DEPENDENT SUBQUERY rdMPGGeraetZubehoerValues ref Bezeichnung Bezeichnung 2 const 6 100.00 Using where
13 DEPENDENT SUBQUERY rdMPGWartung ref Geraet,PersonalID Geraet 4 qmsystems.g.ID 4 27.97 Using where
12 DEPENDENT SUBQUERY rdMPGWartung ref Geraet,PersonalID Geraet 4 qmsystems.g.ID 4 27.97 Using where
11 DEPENDENT SUBQUERY rdMPGWartung ref Geraet,PersonalID Geraet 4 qmsystems.g.ID 4 27.97 Using where
10 DEPENDENT SUBQUERY rdMPGWartung ref Geraet,PersonalID Geraet 4 qmsystems.g.ID 4 27.97 Using where
9 DEPENDENT SUBQUERY rdWache eq_ref PRIMARY PRIMARY 2 qmsystems.g.wartungsWerkstatt 1 100.00 Using index condition
8 DEPENDENT SUBQUERY rdWache eq_ref PRIMARY PRIMARY 2 qmsystems.g.werkstatt 1 100.00 Using index condition
7 DEPENDENT SUBQUERY rf eq_ref PRIMARY PRIMARY 2 qmsystems.g.Funkruf 1 100.00 Using index condition
7 DEPENDENT SUBQUERY f eq_ref PRIMARY PRIMARY 2 func 1 100.00 Using index condition
6 DEPENDENT SUBQUERY rdWache eq_ref PRIMARY PRIMARY 2 qmsystems.g.stammWerkstatt 1 100.00 Using index condition
5 DEPENDENT SUBQUERY f eq_ref PRIMARY PRIMARY 2 qmsystems.g.FesteinbauFID 1 100.00 Using index condition
5 DEPENDENT SUBQUERY rf eq_ref Fahrzeug Fahrzeug 4 qmsystems.f.ID 1 100.00 Using where
4 DEPENDENT SUBQUERY f eq_ref PRIMARY PRIMARY 2 qmsystems.g.StammFesteinbauFID 1 100.00 Using index condition
4 DEPENDENT SUBQUERY rf eq_ref Stammfahrzeug Stammfahrzeug 4 qmsystems.f.ID 1 100.00 Using where
3 DEPENDENT SUBQUERY rf eq_ref PRIMARY PRIMARY 2 qmsystems.g.StammFunkrufID 1 100.00 Using index condition
3 DEPENDENT SUBQUERY f eq_ref PRIMARY PRIMARY 2 func 1 100.00 Using index condition
2 DEPENDENT SUBQUERY tblWebUser eq_ref PRIMARY PRIMARY 4 qmsystems.g.delID 1 100.00  

Attaching the explain as json as well

Generated at Thu Feb 08 09:50:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.