List:General Discussion« Previous MessageNext Message »
From:xutian Date:December 19 2001 7:48am
Subject:Re: Mysql query it too slow in big table
View as plain text  
----- Original Message -----
From: "Dan Nelson" <dnelson@stripped>
To: "xutian" <xutian@stripped>
Cc: <mysql@stripped>
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
> dnelson@stripped


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.


Thread
Mysql query it too slow in big tablexutian19 Dec
  • Re: Mysql query it too slow in big tableRyan Fox19 Dec
  • Re: Mysql query it too slow in big tableDan Nelson19 Dec
  • Re: Mysql query it too slow in big tablexutian19 Dec