Why you shouldn’t use SELECT * ( ALL ) in MySQL

Simple Answer: You will never use a index this way. Lets look at the following examples:

EXPLAIN SELECT * FROM `wp_posts`;
+—-+————-+———-+——+—————+——+———+——+——+——-+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+—-+————-+———-+——+—————+——+———+——+——+——-+
|  1 | SIMPLE      | wp_posts | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+—-+————-+———-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)

Now lets look at the indexes..

SHOW INDEXES IN `wp_posts`;
+———-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+———-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+
| wp_posts |          0 | PRIMARY          |            1 | ID          | A         |           3 |     NULL | NULL   |      | BTREE

So easily we can do the following:

EXPLAIN SELECT ID FROM `wp_posts`;
+—-+————-+———-+——-+—————+———+———+——+——+————-+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+—-+————-+———-+——-+—————+———+———+——+——+————-+
|  1 | SIMPLE      | wp_posts | index | NULL          | PRIMARY | 8       | NULL |    3 | Using index |
+—-+————-+———-+——-+—————+———+———+——+——+————-+
1 row in set (0.00 sec)

You are saying “But I really need all the fields”.. Some things to think about

1. You may need them all now, but by using SELECT *, if the table ever grows, indirectly you will start selecting a lot more data than you originally intended on previously made query’s

2. If you really need them all, take the time now to SELECT them individually. If you want to SELECT the fields post_type, post_status, post_date, ID   from the table  wp_posts, make the following index:

ALTER TABLE `table_name` ADD INDEX (`post_type`,`post_status`,`post_date`,`ID`);

Then check the EXPLAIN

EXPLAIN SELECT post_type, post_status, post_date, ID FROM `wp_posts`;
+—-+————-+———-+——-+—————+——————+———+——+——+————-+
| id | select_type | table    | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+—-+————-+———-+——-+—————+——————+———+——+——+————-+
|  1 | SIMPLE      | wp_posts | index | NULL          | type_status_date | 140     | NULL |    3 | Using index |
+—-+————-+———-+——-+—————+——————+———+——+——+————-+
1 row in set (0.00 sec)

So now you are still selecting the same information, but when your table grows by leaps and bounds, the result will still be instant