Can ordering your WHERE clause improve performance?

sneakyimp

Can I make a query perform faster by putting the easy comparisons first in my WHERE clause? Or perhaps putting the checks on indexed fields first? In other words, if i have a bazillion records in my db table, will this query:

SELECT FROM my_table WHERE
AND indexed_field_1 > 5
AND not_indexed_field IN (1,2,3)
AND (field1field2field3SQRT(field1/field2)ASIN(field4) ATAN(field5) *POWER(field6, 2343433)) < 10

be like to perform faster than this equivalent one that has the nasty calculation first:

SELECT FROM my_table WHERE
AND (field1field2field3SQRT(field1/field2)ASIN(field4) ATAN(field5) *POWER(field6, 2343433)) < 10
AND indexed_field_1 > 5
AND not_indexed_field IN (1,2,3)

Piranha

It won't make any difference in this example if you have a DBMS that is not extremely old. How simple databases (like access) works I don't know, but MySql, MS SQL, Opera and all other DBMSes handles the where statements in a query optimizer that sorts the order of the where statements.

With that said it is possible to optimize queries manually. But unfortunatley it's not to just change the order of the different where statements.

sneakyimp

To think that MySQL would auto-optimize my query sounds awesome. Are you certain? Is there somewhere I can read about this? i'm using MySQL 4.0

Piranha

Here you can read a little about query optimizing in MySql. Just google to get more information, use keyword query optimizing.

sneakyimp

THANKS. I'm definitely reading up on that.