Why doesn't MySQL 8 utilize the fulltext index with MATCH AGAINST in prepared statements while 5.7 does?

[Bob_Clever]

I’m experiencing a problem with MySQL 8 where it doesn’t leverage the fulltext index as expected when using prepared statements, which is different from how MySQL 5.7 behaves.

Here’s the SQL code I’m working with:

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;

In MySQL 8, the first statement utilizes the name full-text index, but the second doesn’t. However, in MySQL 5.7, both queries work correctly. What might be causing this discrepancy?

[emmat83]

Sounds like optimizer changes between versions. MySQL 8’s query planner got pickier about fulltext indexes with prepared statements, especially when you’ve got variables in comparisons like > ?. Try hardcoding the fulltext boolean threshold instead of using a variable - might force it to recognize the index.