- MariaDB Version 10.5.15
- simple Table, two columns with an index
If I make a query on a table and use two columns in the where conndition that have an index, no index is used. Why?
CREATE TABLE `logs` (
`log_id` int(10) NOT NULL AUTO_INCREMENT,
`date` datetime DEFAULT NULL,
`status` int(10) DEFAULT 0,
`text` varchar(255) DEFAULT NULL,
PRIMARY KEY (`log_id`),
KEY `status` (`status`),
KEY `date` (`date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Where date
analyze select count(*)
from logs
where `date` < "2022-01-01 00:00:00"
uses index => 1.4s
id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | logs | range | date | date | 6 | 5416235 | 7969250.00 | 100 | 100 | Using where; Using index |
Where status
analyze select count(*)
from logs
where status = 860
uses index => ~0.9s
id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | logs | ref | status | status | 5 | const | 5416235 | 5536942.00 | 100 | 100 | Using index |
Where date AND status
analyze select count(*)
from logs
where status = 860
and `date` < "2022-01-01 00:00:00"
uses no index => ~7s
id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | logs | ALL | status,date | 10832494 | 11430473.00 | 25 | 43.85 | Using where |