SQL query performance - single record, filtered by non-indexed column, sorted by indexed column, record is close in the sequence of sorted records

user_7719864 0
Viewed 47

I have the following (My)SQL query:

SELECT * FROM table WHERE nidx = x ORDER BY id DESC LIMIT 1

With the following assumptions:

  • id is an indexed field
  • nidx is a non-indexed field (let's say it has a numerical type)
  • x is a constant
  • record with nidx = x is relatively close in the ordered sequence of records (let's say it is guaranted to be somewhere among the first 1000 records in the order)

I have two questions:

  1. Can I assume that this is an efficient query or should I add an index to nidx column?
  2. Does the answer to the first question depends on the specific RDBMS (so it may by different for MySQL, PostgreSQL, MSSQL, SQLite, etc.)? If yes, how it is for MySQL?
database-performance mysql sql database
3 Answers

Ordering is applied after filtering. The ORDER BY clause does not help the seach in this case. Equally, unless you have some clear constraint on the table that indicates the values will be close, the optimiser doesn't know that and it won't help.

What -might- help, if you can't / won't apply an index on nidx is to first get the records around id = x and then search those.

Something like...

SELECT
  *
FROM
  table
WHERE
  id BETWEEN x - 1000 AND x + 1000
  AND nidx = x
ORDER BY
  id
LIMIT
  1

-Hopefully- this will allow the optimiser to build a plan where the 2000 records around id=x are found first, then only those 2000 records manually searched for nidx= x.

You'll have to try it and see, and use EXPLAIN to find out exactly what's being done in what order.


In general, however, this is a hack, don't rely on it too much. Better to fix the indexing.

  • Which is advice for all platforms

Just add the index. :)

Dems_533410

Considering the number of records, index would be preferrable. Example in MySQL:

ALTER TABLE  table ADD INDEX nidx_index (nidx)

You can also create unique index for unique values:

ALTER TABLE  table ADD UNIQUE INDEX nidx_index (nidx)
user_129921460

You could use an index for the nidx field, but you have to keep in mind that this will make the UPDATE, INSERT and DELETE queries more inefficient.

The most penalizing of the sql queries with ORDER BY and GROUP BY, because they are operations that are performed at the end. If it is not necessary, I would remove the ORDER BY

finally you can use the EXPLAIN command to diagnose SQL queries

EXPLAIN SELECT * FROM table WHERE nidx = x ORDER BY id DESC

Here a little tutorial for improve a query using Explain https://dev.mysql.com/doc/workbench/en/wb-tutorial-visual-explain-dbt3.html

user_142037660
Related