Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.12
-
None
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.