Hi James,
I have found similar - slowdown - effects for queries.
However, it is not always clear what causes the lack of speed.
For example, I have a table with more than 9 million rows,
including a non-unique indexed item myKey (tinyint).
The query "Select myKey, count(*) from myTable Group By myKey;"
takes with the CLI about 25 seconds,
BUT the second time it takes only 0.01 second !
I think that the 1st query run includes loading indices into memory.
I suggest to test your query twice from the CLI.
HTH, Cor Vegelin
----- Original Message -----
From: "James Harvard" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Wednesday, January 04, 2006 8:28 PM
Subject: SELECT DISTINCT uses index but is still slow
I have a query that takes a long time even though it is only examining the
index file. Is this normal? I would have thought that MySQL would be much
quicker to get the unique values from an index.
select distinct date_id from data_table;
# returns 89 rows in 23 seconds
- simple index on date_id with 2 other indices
- just under 40,000,000 rows in table
- index file is 730 MB
EXPLAIN SELECT gives the following:
type = index
key = date_id
rows = 39726908
extra = using index
FWIW the result is identical with 'select date_id from data_table group by
date_id;'.
(Using version 4.1.15 on Windows, and I can't see anything relevant in the
change notes for 4.1.16.)
Finally, here's a CREATE TABLE:
CREATE TABLE data_table (
is_import tinyint(1) NOT NULL DEFAULT 0,
comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0,
value bigint(20) UNSIGNED NOT NULL DEFAULT 0,
quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0,
quantity_2 bigint(20) UNSIGNED DEFAULT NULL,
c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0,
c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0,
c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL,
port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
KEY date_id (date_id),
KEY country_id (country_id),
KEY comcode_id (comcode_id,date_id)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
TIA,
James Harvard
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
