[GRLUG] SQL Optimizations
SMachaje at grcc.edu
Thu Jun 21 17:13:53 EDT 2007
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:
>>> "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
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.]
More information about the grlug