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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
