MySQL sorting: NULL to the end & use index? Not possible?

Question

Hello fellow experts,

I have a huge table and I want simple sorting.

It could be so easy. I could just create an index and do some really fast sorting thanks to that index.

But my client wants to put NULLs to the end, which is complicates the whole situation.

Instead of simple: SORT BY name ASC I have to do SORT BY name IS NULL ASC, name ASC. That would be ok, but it because of that my index is useless, and the sorting is very slow.

I don't know if there's a way to solve this problem, but if there is one, I desperately ask for help. :'(

Answer 1

perhaps, instead of null you could give it an arbitrary value, like 'a'. im not an expert on this but that might work?

Answer 2

Use a union to first select those records where name is not null, and then the rest.

Answer 3

UNION ALL is not guaranteed to preserve the record order, but with current implementation the final ORDER BY will amount just to a single pass over already ordered fields:

SELECT  *
FROM    (
        SELECT  1 AS source, *
        FROM    user
        WHERE   name IS NOT NULL
        ORDER BY
                name
        )
UNION ALL
SELECT  2 AS source, *
FROM    user
WHERE   name IS NULL
ORDER BY
        source, name

Omitting the final ORDER BY may break your application in the future.

This is probably one of the rare cases when it's better to split the query in two on the client side.