Mark Leith wrote:
>>From: Michael Stassen [mailto:mstassen@stripped]
>> ORDER BY IF(col IS NULL, 1, 0), col
> Or simply:
> ORDER BY col IS NULL, col
> Which will probably be *slightly* faster..
Михаил Монашёв wrote:
> MS> Something like
> MS> ORDER BY IF(col IS NULL, 1, 0), col
> it's very slowly. Maybe better create 'col', and make index after?
> Михаил Монашёв,
I don't think an index will help here, as the first part of the ORDER BY, "col
IS NULL", causes a filesort. If there is an index on col, and if the query is
one that would otherwise use it, this might be faster
(SELECT col FROM mytable WHERE col IS NOT NULL ORDER BY col)
(SELECT col FROM mytable WHERE col IS NULL);
but it's a little silly. The real query no doubt selects other columns, and
has WHERE conditions on other columns, so mysql will probably not use an index
on col to order the results anyway. Perhaps a multi-column covering index
would help, depending on the specifics of the real query.