----- Original Message ----- From: "Dan Nelson" <[EMAIL PROTECTED]> To: "xutian" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, December 19, 2001 12:27 PM Subject: Re: Mysql query it too slow in big table
> In the last episode (Dec 19), xutian said: > > ###COLL_DATA's index > > mysql> show index from COLL_DATA; > > +-----------+------------+----------------+--------------+-------------+---- -------+-------------+----------+--------+---------+ > > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | > > +-----------+------------+----------------+--------------+-------------+---- -------+-------------+----------+--------+---------+ > > | COLL_DATA | 1 | RELATION_99_FK | 1 | IP_ADDR | A | 86 | NULL | NULL | | > > | COLL_DATA | 1 | RELATION_100 | 1 | INST_OID | A | 2625 | NULL | NULL | | > > | COLL_DATA | 1 | IDX_THE_TIME | 1 | THE_TIME | A | 8226 | NULL | NULL | | > > +-----------+------------+----------------+--------------+-------------+---- -------+-------------+----------+--------+---------+ > > ###query the last time where ip='172.017.011.253' > > mysql> select max(THE_TIME) from COLL_DATA where IP_ADDR='172.017.011.253'; > > +---------------------+ > > | max(THE_TIME) | > > +---------------------+ > > | 2001-11-26 14:35:18 | > > +---------------------+ > > 1 row in set (6.77 sec) > > ~~~~~~~~It's too slowly > > Try creating an index on (IP_ADDR,THE_TIME). Mysql will only use one > index for a table per query. Mysql will be able to use the 2-field > index for both the WHERE and MAX parts of the query and won't have to > touch the table at all. > > > -- > Dan Nelson > [EMAIL PROTECTED] It's no use when I create a index on (IP_ADDR,THE_TIME). I think that maybe the result set of the query is too big to query quickly. I 've test some other query just like this , the result is the same. --------------------------------------------------------------------- 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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
