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