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