hi all, Let me to introduce somethings first, we use mysql for a logging system and take about max 150 000 records hourly. And we need to take a period of data for analyzing. >Well, how many records are in the table? How many would be returned by the >query you present?
The previous table is my trail one ! . Actually, my db (tlcounter200109) contains 56463703 records And for that sql statement : select * from tlcounter200109 where timerecord = 0109051200. The returned records have 86564 rows and execution time is 6 min 1.15 sec ( Expected time is within 1 min) >Is the SQL you show the FULL sql? Yes , full sql is that select * from tlcounter200109 where timerecord = 0109051200 >What is the output of the EXPLAIN SELECT.... explain select * from tlcounter200109 where timerecord = 0109051200; +---------------------- +-------+---------------------+----------+-----------+---------+------------ --+-------------------+ | table | type | possible_keys |key | key_len | ref | rows | Extra | +-----------------------+------ +----------------------+---------+-----------+---------+--------------+----- --------------+ | tlcounter200109 | ALL | timerecordindex | NULL | NULL | NULL | 56463703 | where used | +-----------------------+-------+---------------------+-------- -+---------- -+---------+--------------+---- --------------+ * remark : quate from <http://www.mysql.com/doc/E/X/EXPLAIN.html>, key The key column indicates the key that MySQL actually decided to use. The key is NULL if no index was chosen. If MySQL chooses the wrong index, you can probably force MySQL to use another index by using myisamchk --analyze, See section 4.4.6.1 myisamchk Invocation Syntax, or by using USE INDEX/IGNORE INDEX. See section 6.4.1.1 JOIN Syntax. after i used the comand myisamchk -a , but it seems no any change ! >What about "SHOW INDEX FROM TABLE gw". mysql> show index from tlcounter200109; +---------------------+------------------+---------------------+------------ ------+--------------------+------------+--------------+-------------+------ ----+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---------------------+------------------+---------------------+------------ ------+--------------------+------------+--------------+-------------+------ ----+------------+ | tlcounter200109 | 1 | esnindex | 1 | esn | A | 131924 | NULL | NULL | | | tlcounter200109 | 1 | esnindex | 2 | timerecord | A | 56463703 | NULL | NULL | | | tlcounter200109 | 1 | timerecordindex | 1 | timerecord | A | 618 | NULL | NULL | | +---------------------+------------------+---------------------+------------ ------+---------------------+-----------+---------------+-------------+----- ----+------------+ 3 rows in set (0.00 sec) Would all experts give me any advices ? regards, kmlau --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
