effectiveness of creating index for ORDER BY column when used with many WHERE clauses

Question

Say I am trying to build a pagination for a simple e-commerce app where user can search, filter, and sort items, and the result is displayed in an infinite scroll UI. I'm planning to use the cursor pagination method. When user wants to sort by lowest price (non-unique column), this means the cursor will comprise of ID and the price. Something like:

SELECT * FROM items
WHERE price > {last_price} OR (price = {last_price} AND id > {id})
ORDER BY price ASC, id ASC LIMIT 25

To optimize this query, I can create a compound index (price, id). https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

Now my question is, will this index still be effective if the query contains many WHERE clauses? Because user can filter based on the item's attributes, e.g.

...
WHERE (locationId = 30 AND categoryId = 20 AND price > 5000 ...)     // filtering
AND (price > {last_price} OR (price = {last_price} AND id > {id}))   // cursor pagination clause
...

Based on my understanding, if we don't have the index, then the WHERE clause filtering will be done first to reduce the number of rows, and then ORDER BY is done on the remaining rows. But how's the case when there's an index present for the ORDER BY columns? Will it be useful at all or causing performance issue instead?

Answer 1

In general, the is "no general solution".

The problem is in the limitations of INDEXes.

WHERE locationId = 30 AND categoryId = 20 AND price > 5000

Is best handled by

INDEX(locationId, categoryId,   -- the order does not matter
      price)   -- normally the last column that will used.

That is, put the = columns first, then one "range" test.

In this particular case, you fall into

ORDER BY price ASC, id ASC

So now I recommend

INDEX(locationId, categoryId,   -- the order does not matter
      price, id)   -- order DOES matter

In this case, the Optimizer is likely to get to the LIMIT 25 and read only 25 rows.

But, this is possibly not the common case for your app. And/or it would require too many different INDEXes. (There is a limit of only 16.) Without the 'perfect' index, the filtering (WHERE) is performed and the results are stored in a temp table which is then sorted (ORDER BY). Finally the 25 are peeled off.

Maybe "location" and "category" are very commonly used. So, maybe filtering on those will shrink the number of rows to be tolerable.

Maybe there are a small number of other columns that are commonly used in WHERE with =. Build a few indexes starting with them. Record all the queries that users request -- this gives you statistics on which to judge which indexes to make.

More discussion: Entity-Attribute-Value

And, yes,

 WHERE price > {last_price} OR (price = {last_price} AND id > {id})

is one of the very few cases where OR is optimized. This assumes an INDEX starting with (price, id). Or, for your example, preceded by = tests.

EXPLAIN and EXPLAIN FORMAT=JSON do not necessarily tell you whether an index is really useful, especially when there is a LIMIT. I like to use this technique:

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

In your example, the largest number will be about 25; this indicates that the INDEX took care of both the WHERE and the ORDER BY. Otherwise, the largest number will be much bigger, possibly the size of the table, and there will probably be some Handler_write... values, indicating a temp table was used.