Thanks for your response,
What benefit would that give me? Would a combined index be faster? I have
the three columns indexed anyway, and do not really want to dedicate more of
my index file to a duplication . it is getting to the 1G mark already, and I
have a 2G limit for any one file.
I have some more information that may prove useful...
If I do a select count(*) with the date set to 2001-02-10 then the soc_date
index is used and the query runs really quickly
mysql> explain select count(*) from log where queue_id = 5 and soc_date =
'2001-02-10';
+-------+------+--------------------+--------------+---------+-------+------
-+------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-------+------+--------------------+--------------+---------+-------+------
-+------------+
| log | ref | soc_date_idx,q_idx | soc_date_idx | 4 | const | 42558
| where used |
+-------+------+--------------------+--------------+---------+-------+------
-+------------+
1 row in set (0.00 sec)
However, if I do a select count(*) with the date set to 2001-02-11 then the
q_idx is used and the query runs very slowly.
mysql> explain select count(*) from log where queue_id = 5 and soc_date =
'2001-02-11';
+-------+------+--------------------+-------+---------+-------+-------+-----
-------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+--------------------+-------+---------+-------+-------+-----
-------+
| log | ref | soc_date_idx,q_idx | q_idx | 5 | const | 58410 |
where used |
+-------+------+--------------------+-------+---------+-------+-------+-----
-------+
1 row in set (0.00 sec)
Here are the real life results:
mysql> select count(*) from log where queue_id = 5 and soc_date =
'2001-02-10';
+----------+
| count(*) |
+----------+
| 11 |
+----------+
1 row in set (0.89 sec)
mysql> select count(*) from log where queue_id = 5 and soc_date =
'2001-02-11';
+----------+
| count(*) |
+----------+
| 17 |
+----------+
1 row in set (1 min 52.85 sec)
Here are the results of the explains if the queue_id is taken out of the
where. The rows estimate is fairly similar.
mysql> explain select count(*) from log where soc_date = '2001-02-11';
+-------+------+---------------+--------------+---------+-------+-------+---
----------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+---------------+--------------+---------+-------+-------+---
----------------------+
| log | ref | soc_date_idx | soc_date_idx | 4 | const | 60410 |
where used; Using index |
+-------+------+---------------+--------------+---------+-------+-------+---
----------------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from log where soc_date = '2001-02-10';
+-------+------+---------------+--------------+---------+-------+-------+---
----------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+---------------+--------------+---------+-------+-------+---
----------------------+
| log | ref | soc_date_idx | soc_date_idx | 4 | const | 42564 |
where used; Using index |
+-------+------+---------------+--------------+---------+-------+-------+---
----------------------+
1 row in set (0.00 sec)
Is it just tipping the balance and making the optimiser choose a different
index?
If so, why does the select against the queue_index take so long?
Thanks for your help in advance,
Robin
-----Original Message-----
From: Patrick FICHE [mailto:[EMAIL PROTECTED]]
Sent: 20 February 2001 14:26
To: Robin Keech
Subject: RE: Slow Select count(*) - Second Post
Hi,
What do you think of creating a combined index containing fro example
soc_date, server_id, queue_id ) in this order...
It depends of course of your application as the index choice is usually one
of the most difficult tasks when designing a database.
Patrick
---------------------------------------------------------------------
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
