"ORDER BY column=value" using indexes (no full table scan)

Question

I'm trying to hack a FLOSS application called Phabricator / Phorge

Let's take this simple MySQL table storing some questions by ID and their status (open, closed, invalid etc.):

CREATE TABLE `ponder_question` (
  `id`         int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(32)          NOT NULL,
  PRIMARY KEY   (`id`),
  KEY `status`  (`status`),

I want to order by a specific status first, then the others. So:

SELECT * FROM ponder_question
  ORDER BY status='open' DESC
  LIMIT 5

It works but consider this DESCRIBE. That query is apparently examining 5000 rows which is probably too much / it is doing a full table scan:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ponder_question	NULL	index	NULL	status	130	NULL	5000	100.00	Using index; Using filesort

(Instead, if you EXPLAIN a simple ORDER BY status DESC LIMIT 5 it examines just 5 rows)

To reduce the number of examined rows and avoid that Using filesort I also tried a FORCE INDEX:

SELECT * FROM ponder_question
  FORCE INDEX (status)
  ORDER BY status='open' DESC
  LIMIT 5

I tested in MySQL 5.7 and MariaDB 10.3. Anyway, my question is not version-specific.

I guess I need to avoid this approach.

I maybe need to change the schema to have a simpler ordering clause, but maybe not.

To be honest, the final goal is to ORDER BY status='open' DESC, id, so showing all open questions by creation, and then all closed questions by creation.

I think this approach is bad, but I was just trying to be nice with this application, without proposing a schema change. So feel free to tell me that my question is stupid:

Question: how do you implement an order by a value first, in a more efficient way?

Some related similar approaches that apparently do not describe this problem:

How does ORDER BY FIELD() in MySQL work internally

You use ordering by an expression (ORDER BY status='open' DESC). This sorting cannot use the index. Rather than ORDER BY status DESC LIMIT 5 which can and use the index. If you want the index to be used (which makes no sense for a table with 5k rows) then create an index by needed expression or, if the version cannot, by virtual generated column. — Akina, Feb 10 '23 at 11:53

Rick James · Accepted Answer · 2023-02-11T16:52:43.050

5

"Using index" means that the index it used is "covering". That means that all the columns needed (status and id) are in the one index. The index is declared INDEX(status), but implicitly the PRIMARY KEY is tacked on.

Virtually any function, and in many cases any "operator" (eg '=') makes the ORDER BY not sargable . So it cannot use the index to stop after the given LIMIT. However, because of "covering", it does use the index's BTree instead of the data's BTree.

For a small table (under, say, 1K rows), I would not worry about performance.

If the table is big enough (over, say, 1M rows), then there is a more complex way to efficiently do what you asked for:

( SELECT  1 AS seq,
          *
      FROM ponder_question
      WHERE status >= 'open'
      LIMIT 5
) UNION ALL
( SELECT  2 AS seq,
          *
      FROM ponder_question
      WHERE status < 'open'
      LIMIT 5
) ORDER BY seq
  LIMIT 5   -- Yet again

Each of the SELECTs will use INDEX(status), but stop after 5 rows.

The UNION will have up to 10 rows, which is then trivially sorted.

edited Feb 11 '23 at 16:52

answered Feb 10 '23 at 23:10

Rick James

78,038
5
47
113

Thank you for this approach. This is very inspiring. Unfortunately lot of software are designed with weird query builders and abstraction layers that are somehow inflexible, and often, they cannot generate an UNION ALL query. Also what I don't understand is why the second row has not the seq to 2 or 0 to keep the order (now it's 1 in both queries). Thanks – Valerio Bozz Feb 11 '23 at 10:20
Yeah, don't get me started on query builders and abstraction layers. Once you go beyond a "toy" database, you nave to learn both the layer and the underlying database (if you want performance). – Rick James Feb 11 '23 at 16:52
@ValerioBozz - And, thanks for catching my bug. "2 seq" was needed. – Rick James Feb 11 '23 at 16:53

score 3 · Answer 2 · answered Feb 10 '23 at 16:20

In my old post How does ORDER BY FIELD() in MySQL work internally, the ORDER BY was doing an evaluation of an IF() function call on a FIELD() function call.

In your case, you are doing ORDER BY on an expression against an indexed column. So, it is not surprising that the MySQL Query Optimizer decided on doing an index scan.

In fact, if you tried the approach from other post

SELECT * FROM ponder_question 
ORDER BY IF(status='open',1,0) DESC,id
LIMIT 5;

it would be a full table scan because the need for an index gets ignored once you start calling functions to evaluate the ORDER BY.

Therefore, Query Optimizer made the right call and your approach is the better way (given no changes to your schema)

"ORDER BY column=value" using indexes (no full table scan)

2 Answers2