General SQL

MySQL

check indexes

mysql> show indexes from <TABLE_NAME>;
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| <TABLE_NAME>|          0 | PRIMARY    |            1 | id          | A         |      453370 |     NULL | NULL   |      | BTREE      |         |               |
| <TABLE_NAME>|          0 | unique_md5 |            1 | md5         | A         |      276722 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

check whether query hit index

hit index:

mysql> explain select * from <TABLE_NAME> where md5 = 'eb979a1437e774b943225a7861001f5b';
+----+-------------+-------------+------------+-------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | <TABLE_NAME>| NULL       | const | unique_md5    | unique_md5 | 131     | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

miss index:

mysql> explain select * from <TABLE_NAME> where url = 'eb979a1437e774b943225a7861001f5b';
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | <TABLE_NAME>| NULL       | ALL  | NULL          | NULL | NULL    | NULL | 476382 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)