Why does an indexed column slowly return results when querying for `IS NULL`?

Question

I have a table with 25 million rows indexed accordingly.

But adding the AND status IS NULL clause turns a super fast query into a crazy slow query.

Please help me speed it up.

Query:

 SELECT student_id, grade, status FROM grades WHERE class_id = 1 AND status IS NULL -- This line delays results from <200ms to 40-70s! AND grade BETWEEN 0 AND 0.7 LIMIT 25; 

Table:

 CREATE TABLE IF NOT EXISTS `grades` ( `student_id` BIGINT(20) NOT NULL, `class_id` INT(11) NOT NULL, `grade` FLOAT(10,6) DEFAULT NULL, `status` INT(11) DEFAULT NULL, UNIQUE KEY `unique_key` (`student_id`,`class_id`), KEY `class_id` (`class_id`), KEY `status` (`status`), KEY `grade` (`grade`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

Local development shows results instantly (<200 ms). The production server is a huge decline (40-70 seconds!).

Can you point me in the right direction for debugging?

I explain:

 +----+-------------+--------+-------------+-----------------------+-----------------+---------+------+-------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------------+-----------------------+-----------------+---------+------+-------+--------------------------------------------------------+ | 1 | SIMPLE | grades | index_merge | class_id,status,grade | status,class_id | 5,4 | NULL | 26811 | Using intersect(status,class_id); Using where | +----+-------------+--------+-------------+-----------------------+-----------------+---------+------+-------+--------------------------------------------------------+ 

Answer 1

Operator

A SELECT can use only one index per table.

Presumably, a request before doing a check using a single class_id index for your condition class_id=1 . Which is likely to filter your result well before checking other conditions.

The improper optimizer chooses to merge the indexes on class_id and status for the second query and checks lines 26811, which are probably not optimal. You could hint at the class_id index by adding USING INDEX (class_id) at the end of the FROM . You can get some joy with a composite index on (class_id,status,grade) , which can run the query faster because it can match the first two, and then scan the grade range. I'm not sure how this works with null .

I assume that ORDER BY forced the optimizer to select the class_id index class_id and returned your original speed.