read

Here's a quick tip for using explain:

You may know this already, but mysql will actually execute some subqueries when you invoke explain.  Here's an example:

mysql> explain select id from projects where id = (select max(id) from projects where name like 'en%');
+----+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows  | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------+
|  1 | PRIMARY     | projects | const | PRIMARY       | PRIMARY | 4       | const |     1 | Using index |
|  2 | SUBQUERY    | projects | ALL   | NULL          | NULL    | NULL    | NULL  | 67922 | Using where |
+----+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------+
2 rows in set (0.11 sec)

Take a look at the execution time (I choose an intentionally poorly executing query for my little dataset).
Here's explain when it's not executing:

mysql> explain select max(id) from projects where name like 'en%';
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | projects | ALL  | NULL          | NULL | NULL    | NULL | 69513 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

If you want to work around this to prevent trouble on a production server (albeit, not getting the output from explain), you can do this:

mysql> set session max_join_size=1;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select id from projects where id = (select max(id) from projects where name like 'en%');
ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
Blog Logo

Fernando Ipar


Published

Image

Fernando Ipar

"Tell my wife I love her very much, she knows"

Back to Overview