I m 采用Mysql 8.0.33。

我创建了一个包含以下表述的复合指数:column_name is not void,并且无法使用该指数。

我做了什么错误?

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:(aappointments.user_id = 7) and (appointments.recurrence_rule is not void) (cost=150.25 rows=135) (actual time=0.356.0.625 rows=4 loops=1) -> Table scan on accreditation (cost=150.25 rows=1500) (actual time=0.143.0.528 rows=1500 loops=1)

Details on use case

我有一个数据库表,包含数百万人的任命。 这些任命中有一些重复(其中只有1%)并按重复模式加以界定。

我要高效地询问属于用户和重复任用的所有任用。 用户可能拥有10 000个任用,其中100个是重复任用。

为此,我制定了关于以下表述的综合指数:<代码>recurrence_rule is notrn和user_id。 但这一指数并未使用。 尤其是,似乎我可以拿到<代码>recurrence_rule is notrn,以便与指数一致。

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;