List:General Discussion« Previous MessageNext Message »
From:xutian Date:December 19 2001 1:25am
Subject:Mysql query it too slow in big table
View as plain text  
###table struct of COLL_DATA
mysql> desc COLL_DATA; 
+-------------+--------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+-------------+--------------+------+-----+---------+-------+ 
| TASK_ID | bigint(20) | YES | | NULL | | 
| E_TYPE_ID | int(11) | YES | | NULL | | 
| IP_ADDR | varchar(50) | YES | MUL | NULL | | 
| ERR_TYPE_ID | int(11) | YES | | NULL | | 
| INST_OID | varchar(128) | YES | MUL | NULL | | 
| INST_VALUE | varchar(128) | YES | | NULL | | 
| INST_TYPE | int(11) | YES | | NULL | | 
| RSLT_STATUS | int(11) | YES | | NULL | | 
| THE_TIME | datetime | YES | MUL | NULL | | 
| THE_SN | bigint(20) | YES | | NULL | | 
| IS_SUCCESS | decimal(1,0) | YES | | NULL | | 
+-------------+--------------+------+-----+---------+-------+ 
11 rows in set (0.00 sec) 

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

3 rows in set (0.00 sec) 


###COLL_DATA? record count 
mysql> select count(*) from COLL_DATA; 
+----------+ 
| count(*) | 
+----------+ 
| 1357454 | 
+----------+ 
1 row in set (0.00 sec) 


###query the last time
mysql> select max(THE_TIME) from COLL_DATA; 
+---------------------+ 
| max(THE_TIME) | 
+---------------------+ 
| 2001-11-26 14:38:05 | 
+---------------------+ 
1 row in set (0.00 sec) 


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

mysql> explain select max(THE_TIME) from COLL_DATA where IP_ADDR='172.017.011.253'; 
+-----------+------+----------------+----------------+---------+-------+--------+------------+

| table | type | possible_keys | key | key_len | ref | rows | Extra | 
+-----------+------+----------------+----------------+---------+-------+--------+------------+

| COLL_DATA | ref | RELATION_99_FK | RELATION_99_FK | 51 | const | 669429 | where used | 
+-----------+------+----------------+----------------+---------+-------+--------+------------+

1 row in set (0.00 sec) 
~~~~~~~~~~~~~~~only used ip_addr as index 

###Benchmark 
mysql> select benchmark(1000000,1+1); 
+------------------------+ 
| benchmark(1000000,1+1) | 
+------------------------+ 
| 0 | 
+------------------------+ 
1 row in set (0.13 sec) 




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