you are right
I tried specifying it to use the crcid index with "USE INDEX (crcindex)" in
the from clause but it made no difference. any ideas as to which indexes I
could create to speed up the second query.
Here is an explain on my test database which is significantly smaller:
mysql> explain 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;
+-------+------+------------------+----------+---------+---------+-------+--
---------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+------------------+----------+---------+---------+----------
--------------------------+
| a | ALL | PRIMARY,crcindex | NULL | NULL | NULL | 73665 |
where used; Using temporary |
| b | ref | PRIMARY,crcindex | crcindex | 4 | a.crcid | 3 |
where used |
+-------+------+------------------+----------+---------+---------+-------+--
---------------------------+
2 rows in set (0.00 sec)
Roger kanrouk
-----Original Message-----
From: Braxton Robbason [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 11:55 AM
To: Roger Karnouk; [EMAIL PROTECTED]
Subject: RE: Query speed
seems to me that the first query uses your primary key index. Since you have
specified qualifications on crcid and tag in both aliases, it will resolve
to a small number of rows in each alias table. The second query will join
your aliases on the crcid index, and then the tag qualifications will
resolve to a larger number of rows. A way to verify this is to run:
select count(*) as rcount
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 ;
and
select count(*) as rcount
from server0000000001_history a, server0000000001_history b
where a.crcid = b.crcid
and a.tag = 100 and b.tag = 104;
I bet the latter rcount value is much greater than the former. Did you run
explains on these queries?
braxton
-----Original Message-----
From: Roger Karnouk [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 9:57 AM
To: [EMAIL PROTECTED]
Subject: Query speed
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
---------------------------------------------------------------------
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
