MySql Order by isnull() Performance Problem my sql below is use for listing stock added from 10 days ago.
Order by isnull(Price) is use so that stock without any price yet will still being listed.

AddDate and Price has an index.

SELECT Id, Price FROM tblStock
where AddDate &gt;= date_sub(curdate(),interval 10 day)
order by isnull(Price), Price asc limit 50


Explain the sql shown that it is not using the Price index.
So i tried to improve the query and has come out with below sql

SELECT Id, Price FROM tblStock
where AddDate &gt;= date_sub(curdate(),interval 10 day)
and Price is not null
order by Price asc limit 50


The new sql works a lot faster and explain shown it uses Price index but the problem is that Price with null values will never selected. 

Looking for any comments or suggestions on how to solve this problem. Thanks.
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.