simonrenoult

class Article {
    private Integer article_id;
    private String article_title;
    private String article_content;
}

simonrenoult

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM articles IGNORE INDEX(PRIMARY) WHERE article_id = 150000;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | articles | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996520 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

simonrenoult

mysql> SELECT SQL_NO_CACHE * FROM articles IGNORE INDEX(PRIMARY) WHERE article_id = 150000;
+------------+---------------+-----------------+
| article_id | article_title | article_content |
+------------+---------------+-----------------+
| 150000     | Title 150000  | Content 150000  |
+------------+---------------+-----------------+
1 row in set, 1 warning (0.65 sec)

simonrenoult

mysql> SELECT SQL_NO_CACHE * FROM articles USE INDEX(PRIMARY) WHERE article_id = 150000;
+------------+---------------+-----------------+
| article_id | article_title | article_content |
+------------+---------------+-----------------+
| 150000     | Title 150000  | Content 150000  |
+------------+---------------+-----------------+
1 row in set (0.62 sec)

simonrenoult

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM articles WHERE article_id = 150000;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | articles | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 996520 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 4 warnings (0.00 sec)

simonrenoult

mysql> OPTIMIZE TABLE articles;
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table              | Op       | Msg_type | Msg_text                                                          |
+--------------------+----------+----------+-------------------------------------------------------------------+
| debug_sql.articles | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| debug_sql.articles | optimize | status   | OK                                                                |
+--------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (10.46 sec)

simonrenoult

mysql> SELECT SQL_NO_CACHE * FROM articles WHERE article_id = 150000;
+------------+---------------+-----------------+
| article_id | article_title | article_content |
+------------+---------------+-----------------+
| 150000     | Title 150000  | Content 150000  |
+------------+---------------+-----------------+
1 row in set, 1 warning (0.65 sec)

simonrenoult

mysql> ANALYZE TABLE articles;
+--------------------+---------+----------+----------+
| Table              | Op      | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| debug_sql.articles | analyze | status   | OK       |
+--------------------+---------+----------+----------+
1 row in set (0.02 sec)

simonrenoult

mysql> SELECT SQL_NO_CACHE * FROM articles WHERE article_id = 150000;
+------------+---------------+-----------------+
| article_id | article_title | article_content |
+------------+---------------+-----------------+
| 150000     | Title 150000  | Content 150000  |
+------------+---------------+-----------------+
1 row in set, 1 warning (0.80 sec)

simonrenoult

mysql> SHOW INDEX FROM articles;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| articles |          0 | PRIMARY  |            1 | article_id  | A         |     1158879 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)