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]
