From: Dan Nelson
Date: December 19 2001 4:27am
Subject: Re: Mysql query it too slow in big table
List-Archive: http://lists.mysql.com/mysql/94368
Message-Id: <20011219042721.GA96391@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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