List:General Discussion« Previous MessageNext Message »
From:James Harvard Date:January 4 2006 8:28pm
Subject:SELECT DISTINCT uses index but is still slow
View as plain text  
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
Thread
Calendar table workaroundJonathan Mangin4 Jan
  • Re: Calendar table workaroundJonathan Mangin4 Jan
    • Re: Calendar table workaroundPeter Brawley4 Jan
      • SELECT DISTINCT uses index but is still slowJames Harvard4 Jan
        • Inconsistent rows returned & examined in slow query logJames Harvard4 Jan
  • RE: Calendar table workaroundJay Paulson \(CE CEN\)4 Jan
  • Re: Calendar table workaroundJonathan Mangin4 Jan
  • Re: SELECT DISTINCT uses index but is still slowC.R.Vegelin5 Jan
    • Re: SELECT DISTINCT uses index but is still slowJames Harvard5 Jan