List:General Discussion« Previous MessageNext Message »
From:James Harvard Date:January 5 2006 3:39pm
Subject:Re: SELECT DISTINCT uses index but is still slow
View as plain text  
0.01 seconds is so fast that I wonder if that's actually because the query cache is
storing the query. Do you have query cache enabled?
James

At 6:35 am +0000 5/1/06, C.R.Vegelin wrote:
>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
>
>
>>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