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