[GRLUG] SQL Optimizations

[Szymon Machajewski]

Depending on what SQL database you are using this may be resolved differently.

In SQL more important is on how many rows calculations need to take place, not so much which condition is evaluated first.
This is why you want to have indexes on columns used in the where clause.  Also if possible do where conditions on numerical values (numerical primary keys etc).

In Oracle preparation of a SQL statement is a careful process of calculating odds and optimizing disk reads.  So the first time you run a SQL the database will re-order your where statement automagically and then re-run the cached version.  The optimizer is a powerful database tool.

Here are MySQL notes:
http://dev.mysql.com/doc/refman/5.0/en/optimization.html 



>>> "David Szostek" <dave at uvhosting.com> 6/21/2007 4:34 PM >>>
If any condition does not match, it will move on

  _____  

From: grlug-bounces at grlug.org [mailto:grlug-bounces at grlug.org] On Behalf Of
Justin Denick
Sent: Thursday, June 21, 2007 4:31 PM
To: grlug at grlug.org 
Subject: [GRLUG] SQL Optimizations


Does it matter what question you ask first

Like if the condition is likely to be true we should than ask if it is false

SELECT * FROM db.table WHERE 
flag ISNULL --most flags are null
AND some more criteria here  --Hopefully only asked if the answer to the
preceding question was yes 

So I guess my question is this does the SQL interpreter ask the questions in
order, or all at once, or is the order arbitrary.

I would think that if the first condition is false (i.e. flag NOT NULL) than
don't bother asking any more questions. 
Beat it, move on, get going, kick rocks punk.







-- 
In vino veritas.
        [In wine there is truth.]
                -- Pliny