Slow Query.

[Kold]

Hi.

I have written a query which is super slow. Basically I have 2 tables;

tblResult;
Ref_ID (int)
Returned (tinytext)

tblInfo;
ID (int)
DateEntered (timestamp)

I need to get IDs (tblResult.Ref_ID and tblInfo.ID are comparible) where the Returned field isnt 'I' or 'U', and the date is between 2 set dates (entered by user).

I have thought up the following query;

SELECT tblResult.Ref_ID FROM tblResult, tblInfo WHERE tblResult.Returned ='U' OR tblResult.Returned ='I' AND tblResult.Ref_ID = tblInfo.ID AND UNIX_TIMESTAMP(tblInfo.DateEntered) BETWEEN '1142985600' AND '1143072000'

However it is super slow, the PHP script times out (it needs to run 2 similar queries).

Does anyone have any idea how I could speed it up!?

Thanks!!
K.

[Roger_Ramjet]

1. Use a proper join not a comma and put the join condition after it instead of in the where clause.
2. Forget about the unix timestamp: it forces a table scan and the date field must be converted for every row before the comparison can be made. If you are storing a native date type then use date strings in the where clause.
3. Add indexes to the date field and the returned field.

$sql = "SELECT tblResult.Ref_ID FROM tblResult r INNER JOIN tblInfo i ON (r.Ref_ID=i.ID)
     WHERE  (r.Returned='I' OR r.Returned='U') AND i.DateEntered BETWEEN '2006-01-21' AND '2006-02-14'";

When you write a select query you are always looking to reduce the records to choose from as fats as you can, choosing the correct join is the first step. Index columns that will be used for selection criteria frequently; only refrain from doing so if the extra indexes slow table updates down too much. Avoid forcing a type convertion on a column as this will force a table-scan rather than allowing the use of indexes.

Never mix unix timestamps and sql. All database engines have native date functions that work efficiently on native date and time data types so use them. The unix timestamp is an anachronism left over from the sixties when computers were very slow and very feeble.

[Sxooter]

I'm guessing that the unix timestamp conversion can't use an index (not uncommon, many times a function isn't easily applied to an indexed field) assuming you HAVE an index on the date field.

Try something like this:

select * from table where (field1<>'I' AND field1<>'U') and datefield between '2005-01-01' and '2005-02-01'

[Kold]

Thanks! You are a great help!