hi, >Something is really wrong. Can you rebuild your indexes with command >"REPAIR TABLE tlcounter200109" ? What is your MySQL version? It should >use index.
i used mysql server version: 3.23.43 And i want to speed up the test . i choose a part of data from tlcounter200109 ( 56463703 records ) to form a smaller data table tlcounter0105 (10627007 records) . However, it didnot any improvement after ran yr sugguested command "repair tlcounter0105" ( pls see below in detail) . Would U give me more advice ? show index from tlcounter0105; +---------------+------------+-----------------+--------------+------------- +-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---------------+------------+-----------------+--------------+------------- +-----------+-------------+----------+--------+---------+ | tlcounter0105 | 1 | timerecordindex | 1 | timerecord | A | 120 | NULL | NULL | | +---------------+------------+-----------------+--------------+------------- +-----------+-------------+----------+--------+---------+ 1 row in set (0.00 sec) mysql> repair table tlcounter0105 ; +-----------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+--------+----------+----------+ | terayon200109.tlcounter0105 | repair | status | OK | +-----------------------------+--------+----------+----------+ 1 row in set (1 min 40.58 sec) mysql> explain select * from tlcounter0105 where timerecord = '0109010000'; +---------------+------+-----------------+------+---------+------+---------- +------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------------+------+-----------------+------+---------+------+---------- +------------+ | tlcounter0105 | ALL | timerecordindex | NULL | NULL | NULL | 10627007 | where used | +---------------+------+-----------------+------+---------+------+---------- +------------+ 1 row in set (0.00 sec) mysql> -----Original Message----- From: Tonu Samuel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 5:33 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: pls help !! On Wed, 2001-10-31 at 08:52, kmlau wrote: > 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. I haven't followed the thread but do you use INSERT DELAYED already? These are nonblobking INSERT commands which give you freedon to run SELECT's. > >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 | > +-----------------------+-------+---------------------+-------- -+---------- > -+---------+--------------+---- --------------+ Something is really wrong. Can you rebuild your indexes with command "REPAIR TABLE tlcounter200109" ? What is your MySQL version? It should use index. -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Tonu Samuel <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Räpina, Estonia <___/ www.mysql.com --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
