Re: Slow Query for Alphabetically Adjacent Terms

[Rick James]

Interesting.

One thing to try... Add USE INDEX (headIdx) to the first part. (Or FORCE INDEX)

Perhaps this is a case where prefix (333) index does not work well.

Suggest you use >= or <=, else you will miss any exact hits.

Does it exhibit the symptom for all search terms?

You could try this:

SELECT rec_id, heading as head
    FROM master_heading m
    JOIN (
        ( SELECT  heading
            FROM  master_heading
            WHERE  heading > "SEARCH TERM"
            ORDER BY  heading ASC
            LIMIT  5
        ) UNION DISTINCT
        ( SELECT  heading
            FROM  master_heading
            WHERE  heading <= "SEARCH TERM"
           ORDER BY  heading DESC
           LIMIT  5)
         ) u ON m.heading = u.heading
    ORDER BY  head;

Now the inner SELECTs will be "Using index"
Caution: It can give more than 10 results.

Another trick.

# Step 1: back up 5 rows:
SELECT @head := heading
            FROM  master_heading
            WHERE  heading <= "SEARCH TERM"
            ORDER BY  heading DESC
            LIMIT  5, 1;
# Step 2: find the desired 10 rows:
SELECT rec_id, heading as head
    FROM master_heading
    WHERE  heading >= @head
    ORDER BY  heading ASC
    LIMIT  10;

It may, however, succumb to the original problem.
Oh, a bug: If SEARCH TERM is within 5 of the start of the table, it will have a problem.

Or (same logic; just different syntax):

SELECT rec_id, heading as head
    FROM master_heading
    WHERE  heading >= IFNULL( SELECT heading
            FROM  master_heading
            WHERE  heading <= "SEARCH TERM"
            ORDER BY  heading DESC
            LIMIT  5, 1 ), '')
    ORDER BY  heading ASC
    LIMIT  10;

And, problem solved (via IFNULL).