what is the where clause hierarchy in mysql

Question

I faced this question in an interview. They asked is there any hierarchy.
Ex: SELECT * FROM invoice WHERE invoiceID=100 AND grossAmount>2000 AND customerName= 'Adam'
Is there a special hierarchy to add those 3 conditions? Something Like check numeric condition first? Please give me your opinion.

You might want to refer this: http://stackoverflow.com/questions/15167174/how-is-where-clause-in-sql-evaluated — Polynomial Proton, Feb 26 '15 at 20:58
@TheUknown thank you for your link. But it is about conditions like AND , OR. I need the where close reading order — Dil., Feb 26 '15 at 21:02
Thats what it explains, essentially. If you want to further know about operator precedence, then here it is : http://dev.mysql.com/doc/refman/5.6/en/operator-precedence.html — Polynomial Proton, Feb 26 '15 at 21:06
@TheUknown oh wow thank you. that was really helpful.. So as this example query = operator run first and > after that? — Dil., Feb 26 '15 at 21:09
Yes, they would run in written order. Since they have same precendence — Polynomial Proton, Feb 26 '15 at 21:10
You're welcome. I posted as answer and even added the `official` documentation found on oracle's website. — Polynomial Proton, Feb 26 '15 at 21:18

score 2 · Answer 1 · answered Feb 26 '15 at 20:47

2

The query optimizer will look at the conditions in the WHERE clause and evaluate them in whatever order it finds that:

Ensures correctness
Takes advantage of indexes and other information about the DB

For example, if you had an index on invoiceID it might evaluate that first so that it had fewer rows to examine in checking customerName and grossAmount.

Your example is all 'AND' clauses so there is no precedence involved.

answered Feb 26 '15 at 20:47

n8wrl

19,439
4
63
103

Ok.. Thank you. So you are saying if I put indexing it will be easier to put a search order. Usually when I develop my database using mysql workbeanch I add indexing. Is there any way to put indexing within the where close? I know this is a stupid question, but is there? – Dil. Feb 26 '15 at 20:54
1

There is a way to force MySQL use certain indexes (only if they are present in database schema). Look up USE INDEX and FORCE INDEX. And yes, this is veeeery rarely a good idea. – Stanislav Shabalin Feb 26 '15 at 21:32

score 1 · Accepted Answer · answered Feb 26 '15 at 21:17

Here is the official documentation on Oracle's website

In your case, the query will run as its written since = and < have same operator precedence.

SELECT * FROM invoice WHERE (invoiceID=100 AND grossAmount>2000 AND customerName= 'Adam')

If it was an OR clause

 SELECT * FROM invoice WHERE (invoiceID=100) OR (grossAmount>2000 AND customerName= 'Adam')

Then the AND would run first and then OR. Only in cases where same operators exists then it gets to = + etc. Check documentation for order.

what is the where clause hierarchy in mysql

2 Answers2