Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You want null prices on the top of your list or on the bottom? –  mfouilleul May 31 '13 at 6:52
    
I need null records at the bottom. –  MySql_Newbie May 31 '13 at 11:43
    
And you should have prices sorted in ascendant way, isn't it? –  mfouilleul May 31 '13 at 12:35
    
Price is already sorted in ascending but the nulls should be at bottom. –  MySql_Newbie May 31 '13 at 14:09
add comment

4 Answers

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.

share|improve this answer
    
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. –  MySql_Newbie May 31 '13 at 12:11
add comment

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.

share|improve this answer
    
The problem with your query is that records with null price will come on top of the list instead of on bottom. –  MySql_Newbie May 31 '13 at 12:51
    
That's not the main issue. –  ypercube May 31 '13 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... –  MySql_Newbie May 31 '13 at 14:05
    
Have you added the (Price, AddDate, Id) index? –  ypercube May 31 '13 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... –  MySql_Newbie May 31 '13 at 16:40
show 2 more comments

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.

share|improve this answer
    
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. –  MySql_Newbie May 31 '13 at 12:03
    
I think you have one order by too many, but that's what I'd do too –  TheVedge May 31 '13 at 14:06
add comment

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.

share|improve this answer
    
Because this will show the highest prices (while the OP wants the lowest ones). –  ypercube May 31 '13 at 13:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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