All of your columns are indexed but in an SQL query, only one index can be
used...
So, if you build an index matching exactly the columns used in your query,
you will parse only the lines that match your query...
So, if I'm not wrong, you will only parse 11 or 17 lines depending on your
query and it should be quite immediate.
The only problem is that you can't build as many indexes as you have queries
in your application or your files will become very large and updates will
take longer...
That's why, you really need to globally analyse your application to see
which fields are often used in WHERE clauses.
Patrick
-----Message d'origine-----
De : Robin Keech [mailto:robin@stripped]
Envoyé : mardi 20 février 2001 16:15
À : 'mysql@stripped'
Objet : RE: Slow Select count(*) - Second Post
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:pfiche@stripped]
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 <mysql-thread65904@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-pfiche=prologue-software.fr@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
