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" <james.lists.tech@stripped>
To: <mysql@stripped>
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/mysql?unsub=1
>
>
