List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 19 2001 4:27am
Subject:Re: Mysql query it too slow in big table
View as plain text  
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
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