CODEWITHSUNDEEP
mysqldatabase
user1032531
user1032531

Reputation: 26281

What does MySQL perform first: The `WHERE` clause or the `ORDER BY` clause?

What does MySQL perform first: The WHERE clause or the ORDER BY clause?

The reason I ask is to determine whether I should add an index to a given column.

I have a table such as the following:

| Column    | Type        | Index | 
|-----------|-------------|-------| 
| id        | INT (pk)    | Yes   | 
| listorder | INT         | ??    | 
| data      | VARCHAR(16) | No    | 
| fk        | INT (fk)    | Yes   |

I will often execute queries such as SELECT id, data FROM mytable WHERE fk=12345 ORDER BY listorder ASC. For my data set, it will only result in a small number of records (~5) for a given fk, however, there are many records in the table with many fk values, and many duplicated listorder values spanning the many fk values.

If the WHERE clause is performed first, then I expect I shouldn't add an index to listorder as it will result in UPDATE performance degradation without significant improvement for SELECT.

Upvotes: 2

Views: 1801

Answers (3)

Parfait
Parfait

Reputation: 107587

In SQL processing, the WHERE clause is considered an implicit join statement. In fact it is even equivalent to INNER JOIN among query optimizers. Older ANSI syntax only adopted INNER JOIN in the early 90s. Many older SQL select statements read as below:

SELECT * 
FROM table1, table2
WHERE table1.ID = table2.ID

which later the gold standard is as follows:

SELECT * 
FROM table1
INNER JOIN table2
ON table1.ID = table2.ID

However both statements are equivalent. But many argue INNER JOIN is more human readable. See this hearty SO post on INNER vs WHERE.

Unlike most programming languages, in SQL the order of syntax does not determine order of processing. Ironically though, the last line ORDER BY (unless TOP or LIMIT is declared) is usually the very last step and WHERE among the first just after the FROM clause:

FROM table source
JOIN condition
WHERE condition
GROUP BY expression
HAVING condition
SELECT fields
ORDER BY fields

Essentially, the engine structures the table and/or virtual tables determined by FROM, JOIN, and WHERE clauses. Once that structure is set up, then aggregation, field selection, and ordering is handled. So you could not order the table before you have the table!

Indices help in nearly all aspects of the processing. Setting an index on ORDER BY would not lead to performance degradation. But aligning WHERE and ORDER BY can facilitate sorting optimization. See this MySQL reference. In fact, MySQL is known to leave out indices if not needed.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The WHERE clause is evaluated first. I think this is always true in MySQL, but there might be an occasional exception (at least in other databases there is).

For this query:

SELECT id, data
FROM mytable
WHERE fk = 12345
ORDER BY listorder ASC;

The most practical index is mytable(fk, listorder).

Upvotes: 2

O. Jones
O. Jones

Reputation: 108651

The way SQL (all makes and models of servers) uses indexes to satisfy queries is a little more complex than you're assuming. Usually a query gets satisfied by filtering first (WHERE) then ordering.

For the exact query you showed us, if you have a compound index on (fk, listorder) the SQL engine will be able to use the index to satisfy both clauses of your query. The index will first be random-accessed by the WHERE clause, then it will be already in the order needed to satisfy your sorting clause.

Read this: http://use-the-index-luke.com/

Updating a compound index is not much more expensive than updating a single column index. Either way, using an index is better than having to scan the table for a WHERE operation.

Upvotes: 3

Related Questions