Consider the following SQL:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FULLTEXT name (first_name, last_name)
);

DELIMITER //
CREATE PROCEDURE populate_users()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 100 DO
        INSERT INTO users (first_name, last_name, email, password, created_at, updated_at)
        VALUES (
            CONCAT('FirstName', i),
            CONCAT('LastName', i),
            CONCAT('user', i, '@example.com'),
            'password123',
            NOW(),
            NOW()
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL populate_users();

PREPARE stmt1 FROM 'EXPLAIN SELECT *, MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) as search_score FROM users WHERE MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) > 0';
SET @a = "FirstName6";
SET @b = "FirstName6";
EXECUTE stmt1 USING @a, @b;
DEALLOCATE PREPARE stmt1;

PREPARE stmt2 FROM 'EXPLAIN SELECT *, MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) as search_score FROM users WHERE MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) > ?';
SET @c = "FirstName6";
SET @d = "FirstName6";
SET @e = 0;
EXECUTE stmt2 USING @c, @d, @e;
DEALLOCATE PREPARE stmt2;

PREPARE stmt3 FROM 'EXPLAIN SELECT *, MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) as search_score FROM users USE INDEX (name) WHERE MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) > ?';
SET @f = "FirstName6";
SET @g = "FirstName6";
SET @h = 0;
EXECUTE stmt3 USING @f, @g, @h;
DEALLOCATE PREPARE stmt3;

The first statement uses the name full-text index, but the second and third don’t. Even when telling the optimizer to use the index, it can’t. Why is that?

The code can be run on https://onecompiler.com/mysql/42e4cteew