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 >= 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 >= 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.

  • You want null prices on the top of your list or on the bottom? May 31, 2013 at 6:52
  • I need null records at the bottom. May 31, 2013 at 11:43
  • And you should have prices sorted in ascendant way, isn't it? May 31, 2013 at 12:35
  • Price is already sorted in ascending but the nulls should be at bottom. May 31, 2013 at 14:09

4 Answers 4


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.

  • Hmm... i am thinking if there is a way to include/combine the records with null price in my second query that might solve it. Use some kind of union or use a storedproc to combine 2 query? 2 different query that uses index probably faster than my first query. May 31, 2013 at 12:11

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)
  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
  ( 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.

  • The problem with your query is that records with null price will come on top of the list instead of on bottom. May 31, 2013 at 12:51
  • That's not the main issue. May 31, 2013 at 12:52
  • Your union query produce correct result but it is 0.4-5 seconds slower than my first query. Explain shows it runs 3 query with 1) 'Using where; Using filesort' 2) 'Using where; Using filesort' 3) 'Using filesort'. And thanks for the link to the spacial index question, seems too complicated though... May 31, 2013 at 14:05
  • Have you added the (Price, AddDate, Id) index? May 31, 2013 at 15:27
  • Great! It's a lot faster after i added (Price, AddDate, Id) indexes. But... if i need to include more fields in the selection means i need to add that into the indexes too. Select Id, Price, Name, Description... about 10 fields. Hmm i shall try that... May 31, 2013 at 16:40

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:

    Id, Price 
    AddDate >= date_sub(curdate(),interval 10 day)
order by isnull(Price), Price asc limit 50
    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.

  • My first query does what i want correctly, records with null should appear last on the list. I don't need to see rows containing null after 50 rows. My problem is the first query cannot use index and it is slow. May 31, 2013 at 12:03
  • I think you have one order by too many, but that's what I'd do too
    – ESG
    May 31, 2013 at 14:06

Why do not just sort in descendant way ?

FROM   tblstock 
WHERE  adddate >= Date_sub(Curdate(), INTERVAL 10 day) 
LIMIT  50;

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


  • Because this will show the highest prices (while the OP wants the lowest ones). May 31, 2013 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.