List:General Discussion« Previous MessageNext Message »
From:C.R.Vegelin Date:January 5 2006 6:35am
Subject:Re: SELECT DISTINCT uses index but is still slow
View as plain text  
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
>
> 


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