3
  • 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

1 Answer 1

4
ALTER TABLE logs
    DROP INDEX(status)
    ADD INDEX(status, date)

The added "composite" index will help both

WHERE status = ...

and

WHERE status = ... AND date < ...

and also

WHERE date < ... AND status = ...
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.