I am trying to run two queries which seem to me should execute at abut the
same speed.
My table is setup as follows:
day - number of days since 1970
crcid - a number between 0 and 24
tag - a number used to identify record type
total - the value stored (the rest of the record is just to identify this
value)
the primary key is day,crcid and tag
I also have and index on crcid
and on day seperately
Table contains about 1,000,000 records
This query takes 0.02 sec to execute:
select (a.day+4)%7 as dow,avg(b.total/a.total) as average
from server0000000001_history a, server0000000001_history b
where a.day = b.day and a.crcid = 24 and a.tag = 100
and b.crcid = 24 and b.tag = 104
group by dow;
note: dow is "day of week" which is used to group
This query takes 1min 47 sec to execute:
select a.crcid, avg ( b.total/a.total ) as average
from server0000000001_history a, server0000000001_history b
where a.crcid = b.crcid
and a.tag = 100 and b.tag = 104
group by a.crcid;
Both queries are similar they both alias the same table in order to use two
separate records.
Does anyone know why one query is so much faster than the other, and what
can I do to speed up the second query without slowing down the first.
Roger Karnouk
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
