3

Given this SQL:

SELECT * FROM mytable ORDER BY mycolumn, RAND()

Assuming that mycolumn happens to only contain unique values (and hence, contains enough information to perform the ORDER BY), does MySQL short-circuit the operation and skip evaluating the rest?

nickf
  • 537,072
  • 198
  • 649
  • 721
  • wicked question, never thought about that even being possible, regardless of the underlying DBMS – LorenVS Apr 15 '10 at 00:01
  • Just spend 15 minutes ripping through Postgresql source code, can't seem to pinpoint any exact answer :( Wouldn't even have answered your question with regards to mysql though, I'll be checking back on this though – LorenVS Apr 15 '10 at 00:15
  • It would be an unusual optimization to put in - generally, ORDER BY against multiple columns will be forming groups of rows based on the first column, and then further sorting within those groups based on second column, etc. Given that that's the expected usual usage, why would they optimize for a different usage (where extra columns are apparently superfluous) – Damien_The_Unbeliever Apr 16 '10 at 13:34

2 Answers2

2

I think this is the answer. Mysql uses different plans and can't perform lazy evaluation (o "hort-circuit").

mysql> explain select * from avatar  order by id;
+----+-------------+--------+-------+---------------+---------+---------+------+-------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows  | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+-------+-------+
|  1 | SIMPLE      | avatar | index | NULL          | PRIMARY | 8       | NULL | 28777 |       |
+----+-------------+--------+-------+---------------+---------+---------+------+-------+-------+
1 row in set (0.00 sec)

mysql> explain select * from avatar  order by id, name;
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | avatar | ALL  | NULL          | NULL | NULL    | NULL | 28777 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)
mysql> explain select * from avatar  order by id, RAND();
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | avatar | ALL  | NULL          | NULL | NULL    | NULL | 28782 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
1 row in set (0.00 sec)
Andrey Frolov
  • 1,534
  • 10
  • 19
0

Experience shows that it does not, even if mycolumn is the primary key.

newtover
  • 31,286
  • 11
  • 84
  • 89