MySql 8 composite index with the indexed expression "is not null" not being used

I’m using Mysql 8.0.33.

I created a composite index with the expression column_name is not null and can’t get the index to be used.

What am I doing wrong?

CREATE TABLE appointments 
(
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  recurrence_rule VARCHAR(255)
);

CREATE INDEX idx1
ON appointments ((recurrence_rule IS NOT NULL), user_id);

explain analyze select * from appointments where user_id = 7 and recurrence_rule is not null;

-> Filter: ((appointments.user_id = 7) and (appointments.recurrence_rule is not null)) (cost=150.25 rows=135) (actual time=0.356..0.625 rows=4 loops=1) -> Table scan on appointments (cost=150.25 rows=1500) (actual time=0.143..0.528 rows=1500 loops=1)

Details on use case

I have a database table that contains millions of appointments. Some of these appointments repeat (let’s say 1% of them) and are defined by a repeating pattern.

I would like to efficiently query all appointments that belong to a user and are of the repeating type. A user might have 10,000 appointments where 100 are of the repeating type.

To this end, I created a composite index on the expression recurrence_rule is not null and on user_id. But, this index is not used. In particular, it doesn’t seem like I can get the recurrence_rule is not null to match with the index.

Generate sample data

DELIMITER $$

CREATE PROCEDURE GenerateData()
BEGIN
    -- Declare variables
    DECLARE i INT DEFAULT 1; -- Loop counter
    DECLARE user_id INT;
    DECLARE recurrence_rule VARCHAR(255);
    
    -- every 25 appointments is a recurring appointment
    -- each user gets 100 appointments
    WHILE i <= 1500 DO
        SET user_id = CEIL(i / 100.0); -- Calculate user_id, ensure it's rounded up
        
        -- Check if the appointment is recurring
        IF i % 25 = 0 THEN
            SET recurrence_rule = CONCAT('FREQ=', ELT(FLOOR(1 + RAND() * 4), 'DAILY', 'WEEKLY', 'MONTHLY', 'YEARLY'), ';INTERVAL=', FLOOR(1 + RAND() * 10));
        ELSE
            -- Set recurrence_rule to NULL for non-recurring appointments
            SET recurrence_rule = NULL;
        END IF;
        
        -- Insert data into appointments table
        INSERT INTO appointments (user_id, recurrence_rule)
        VALUES (user_id, recurrence_rule);
        
        -- Increment the loop counter
        SET i = i + 1;
    END WHILE;
    
END$$

DELIMITER ;

call GenerateData;