MySql Order by isnull() Performance Problem

Your original clause:

order by isnull(Price), Price asc limit 50

Uses a function on the Price column. In your case, the function is isnull(); but it does not matter - any function on your column, e.g. COALESCE(Price, ...), Price + 1, etc., would make for the same result.

You see, once there's a function on a column, MySQL is then unable to use that column. Consider: the index uses a BTREE on Price, indexing your millions of rows in ascending order. But your request, to order by isnull(Price) requests an altogether different thing: you just want to provide with NULLs or not-NULLs; well, MySQL will not use the index for that.

I suppose it would be nice of MySQL to recognize a set of "safe" functions, which are monotonic with the index; but MySQL gives up an index the moment it sees a function. It just does not predict how the result of the function looks like.

Other databases support Index functions; the MariaDB fork supports virtual columns and indexes on virtual columns -- both solution can help you out with your query. Plain MySQL just can't eat it.

I wonder why you have this complex ORDER BY. I think this is enough:

SELECT Id, Price 
FROM tblStock
WHERE AddDate >= date_sub(curdate(), interval 10 day)
ORDER BY Price ASC 
  LIMIT 50 ;

The real problem is how to optimize this. Since it has one range condition (on column (AddDate) in the WHERE clause and an ordering by a different column (Price), it is really hard.

You may get some efficiency improvement with an index on (Price, AddDate, Id) but it's really a shot in the dark. It will sometimes work fast (when many rows with lowest prices have current dates, too) and it will be totally inefficient other times (when the rows with lowest prices have mostly older dates so almost all or all the index will have to be scanned until 50 matches are found.)

I had asked a question about an almost identical query a few months ago and @Jack Douglas has provided a wonderful solution while @Erwin Brandstetter improved it further: : Can spatial index help a “range - order by - limit” query

It's for Postgres but I think it can be modified to work in MySQL.

The NULLS LAST issue is not the main problem as I see it. It can be solved with a UNION:

  ( SELECT Id, Price, 1 AS Ord 
    FROM tblStock
    WHERE AddDate >= date_sub(curdate(), interval 10 day)
      AND Price >= 0 
    ORDER BY Price ASC 
      LIMIT 50
  )
UNION ALL
  ( SELECT Id, Price, 2
    FROM tblStock
    WHERE AddDate >= date_sub(curdate(), interval 10 day)
      AND Price IS NULL
    -- ORDER BY Price ASC 
      LIMIT 50 
  ( 
ORDER BY Ord, Price 
  LIMIT 50 ;

and while the 2nd part will be efficient (using a (Price, AddDate, Id) index), the 1st part has the same issues as your query and my other proposal.

In your first query, both valid values + null values are returned from the database, but because you use the "limit 50", and because NULLs are displayed last, you don't see the rows containing NULL Price.

The correct for for the first query should be:

SELECT 
    Id, Price 
FROM 
    tblStock
where 
    AddDate >= date_sub(curdate(),interval 10 day)
order by isnull(Price), Price asc limit 50
ORDER BY 
    Price IS NULL ASC, 
    Price ASC

The reason for which you first query didn't use the index was because the result of isnull(Price) returned an un-indexed value.

Why do not just sort in descendant way ?

SELECT id, 
       price 
FROM   tblstock 
WHERE  adddate >= Date_sub(Curdate(), INTERVAL 10 day) 
ORDER  price DESC
LIMIT  50;

NULL values are shown, in bottom of your list and index is used = Your three conditions :)

Max.