Mark Leith wrote:
From: Michael Stassen [mailto:[EMAIL PROTECTED]
<snip>
Something like
ORDER BY IF(col IS NULL, 1, 0), col
Michael
Or simply:
ORDER BY col IS NULL, col
Which will probably be *slightly* faster..
Good point.
Михаил Монашёв wrote:
> Hello
>
> 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?
>
> Sincerely,
> Михаил Монашёв,
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)
UNION ALL
(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.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
