Thanks for your replies.
Patrick: I will try the combined index, and see if it takes too much disk
space. Most queries return within 2 seconds, which is acceptable for me.
Therefore I'm not too concerned about the speed increase in using a combined
index as such.....
What I am concerned about is the fact the mysql is so slow when it chooses
the q_idx. If Jarmo is correct then the q_idx is not a very good index and
the optimiser has got it wrong. It has chosen an index based upon the
number of rows returned and not paid any attention to the 'quality' of the
index. I'll try dropping the q_idx and see what that gives me.
Is there any way to re-write SQL to force the optimiser to use a certain
index?
Thanks again
Robin
-----Original Message-----
From: Jarmo Paavilainen [mailto:[EMAIL PROTECTED]]
Sent: 20 February 2001 15:50
To: Robin Keech
Subject: SV: Slow Select count(*) - Second Post
Hi,
Im maybe just stupid but here I go:
The question is, why does MySQL use "soc_date_idx" in the first query and
then "q_idx" in the second one?
It might be that "q_idx" (that is "queue_id" field) has a lot of identical
rows in it and "soc_date_idx" does not. And thereby the "soc_date_idx" is
better as a query limitator than "q_idx".
If you can, kill the "q_idx" index and try the queries again.
Thats all I could think of, let me know if it helped.
// Jarmo
...
> Can anyone please explain the following?
...
> mysql> select count(server_id) from log where server_id = 1 and queue_id =
1 and soc_date = '2001-02-18';
...
> mysql> select count(server_id) from log where server_id = 1 and queue_id =
5 and soc_date = '2001-02-18';
---------------------------------------------------------------------
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
