----- 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

Reply via email to