0

Have this rather simple SELECT on just one table:

SELECT id AS price_id
FROM prices
WHERE product_id IS NOT NULL AND product_id > 1
AND search_name_updated IS NULL
LIMIT 1000

For some reason, the query takes 10+ seconds to run.

The prices table have a bit more than 6 million rows and there are indexes on both the product_id column and the search_name_updated column.

If I drop one of the where clauses, like below, the query takes less than 0.1 seconds to run.

SELECT pri.id AS price_id
FROM wp_wbx_fh_product_prices pri
WHERE product_id IS NOT NULL AND product_id > 1
LIMIT 1000

Same great execution time if I cut it like below, i.e. the search_name_updated column alone isn't the problem.

SELECT pri.id AS price_id
FROM wp_wbx_fh_product_prices pri
WHERE search_name_updated IS NULL
LIMIT 1000
    

Any ideas for why the original query with all 3 where clauses is so slow?

I don't get it since there are indexes on both columns used in the where clause.

2 Answers 2

3
  • INDEX(search_name_updated, product_id) in this order
  • A LIMIT without an ORDER BY gives you an unpredictable set of rows. If you have the above composite index and add ORDER BY product_id, the query will still be efficient. Almost any other combination will lead to a further slowdown.
  • Get rid of product_id IS NOT NULL AND (as Liva suggests). It is unnecessary since > 1 implies that. (The Optimizer may not be smart enough to optimize it out.) IS NULL is similar to = when it comes to the Optimizer.

When building a composite (multi-column) INDEX, start with columns that are tested with = or IS NULL. More: Index Cookbook

To further explain your experiments:

  • The PRIMARY KEY (presumably just id) is tacked onto the end of each secondary key -- so it can reach over into the data's BTree for any other columns that are needed.
  • A "covering" index is one that contains all the columns used anywhere in the SELECT. This is faster because there is not the bouncing back and forth (1000 times or 8M times).
  • Your original query involved 3 columns: One explicitly in the INDEX, id, and a third column. So it was not "covering".
  • Your other tests involved only 2 columns and the indexes you tried were "covering".
  • The index I recommend is a "covering" index since it contains the 3 columns in your query.

Summary: With INDEX(search_name_updated, product_id)

SELECT id AS price_id
    FROM prices
    WHERE product_id > 1
      AND search_name_updated IS NULL
    LIMIT 1000

(The order of WHERE clauses does not matter; the order of columns in the INDEX does matter.)

The execution will go something like

  1. Drill into the index's B+Tree to find the first entry for search_name_updated IS NULL and product_id > 1 and find an id there.
  2. Deliver that id.
  3. Move to the next INDEX entry and deliver that id
  4. Repeat step 3 until there are no more rows satisfy the WHERE, or the LIMIT 1000 has been reached.

Total: 1000 rows read from just the index's BTree.

All other formulations involve either a table scan a sort or bouncing between the two BTrees.

If you had

SELECT *
    FROM prices
    WHERE product_id > 1
      AND search_name_updated IS NULL
    LIMIT 1000

The execution would be similar, except that the index is no longer "covering". Still, it will be reasonably fast -- because it hits only 1000 rows in the index BTree and only 1000 rows in the data BTree.

1
  • Thanks a lot for the detailed explanation. I understand things much better now.
    – Mads
    Feb 11, 2023 at 18:47
1
  1. I suggest that you create an index on both columns together , put the product_id first. The optimizer usually picks only one index for WHERE condition.
  2. you can get rid of the condition "product_id IS NOT NULL", as soon as you filter it by value NULL are out
2
  • When making a composite index, pick = tests first. If you put product_id first, it won't use the other column.
    – Rick James
    Feb 11, 2023 at 16:24
  • 1
    Thanks a lot. Didn't know the optimizer usually only picked one index.
    – Mads
    Feb 11, 2023 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.