[SOLVED] Can MySQL satisfy a query from a prefix index?

Can MySQL satisfy a query from a prefix index?

I wonder if a covering index can help, for some rows, satisfy a query from a LONGTEXT or any other LOB column? (MySQL 8, MariaDB 10.5)

I have this table (WordPress-defined):

CREATE TABLE wp_options (
    option_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    option_name VARCHAR(191) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    option_value LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    autoload VARCHAR(20) NOT NULL DEFAULT 'yes' COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (option_id) USING BTREE,
    UNIQUE INDEX option_name (option_name) USING BTREE,
    INDEX autoload (autoload) USING BTREE
) ENGINE=InnoDB;

And I need to run this query:

SELECT option_name, option_value FROM wp_options WHERE autoload='yes';

It returns several hundred rows (in many busy WordPress installations). Most of those rows have fairly short option_name, option_value strings, but a few can have long strings. WordPress uses this query a lot (on every page view). It uses the autoload index to satisfy the WHERE condition.

My question: If I define a covering index containing prefixes something like this:

 wp_options(autoload, option_name(40), option_value(131))

can the index scan satisfy the query directly for the shorter names and values? Or does MySQL look in the main table for every option_name and the LONGTEXT objects for every option_value, whether it's short or not?

Solution

You cannot get a covering index benefit from a prefix index.

Even if the values you need fit within the prefix, MySQL doesn't know that at the time it makes the optimizer's plan. It makes the optimizer plan before it reads any rows of data, so it must assume that on at least some of the rows, the value in that column will be longer than the prefix, and it will have to read the remainder of the string from outside the index. So it does not take advantage of the covering index effect by reading only from the index.

There's a demonstration of it here: https://www.percona.com/blog/2006/11/23/covering-index-and-prefix-indexes/ That's an old blog, so if anyone wonders if the same applies on a current version of MySQL, the steps shown in the blog are easy to reproduce.