0

At the mysql query of a large table (1 million records) with dual sorting and limit, there are lost some records. For example:

SELECT rec.id, rec.Price, rec.RegionName, rec.isPayed, rec.isTop 
FROM Records rec
LEFT JOIN Region reg ON reg.RegionID = rec.RegionID 
WHERE rec.Type = "Apartment" AND rec.Transaction = "Sale" AND rec.IsPublished = 1 
ORDER BY rec.isTop DESC, rec.Price ASC 
LIMIT 160, 20

I see the solution only through the union ... but maybe you know better solution?

Thanks!

nice
  • 61
  • 4
  • 1
    What is your current output and how are the "lost records" causing you to see something other than what you expect? – Tim Biegeleisen Oct 23 '15 at 09:25
  • For example https://habrastorage.org/files/db8/49c/c88/db849cc888d64655ad6a314137aa540f.png And afrer sorting i cant find row with id=6 on any of the pages – nice Oct 23 '15 at 10:12
  • Are you sure that `id=6` records should be appearing? Maybe the `WHERE` clause is filtering these out on the conditions you have. – Tim Biegeleisen Oct 23 '15 at 10:16
  • Yes, i'm shure. When I remove one of sorting - result is correct. – nice Oct 23 '15 at 10:18
  • 1
    I think it's because of the peculiarities of mysql. First applied the limit and then make sorting. When isTop records more than 20 he loses some ... – nice Oct 23 '15 at 10:22
  • MySQL orders first then limits, q.v. [this SO article](http://stackoverflow.com/questions/4708708/mysql-order-by-limit). – Tim Biegeleisen Oct 23 '15 at 10:28
  • Yes then I make one order by. But now i'm not shure with two... – nice Oct 23 '15 at 10:30
  • The number of orders should not make a difference. Check your `WHERE` clause. – Tim Biegeleisen Oct 23 '15 at 10:38
  • The fact is that WHERE is correct and removing only one of the sorts I see recording id=6 on one of pages. Frankly speaking, that's why I'm asking :) – nice Oct 23 '15 at 12:24
  • How about trying this: Add `id =6` to your `WHERE` clause and see what you get back. Maybe the sorting is also chopping off the records with `id=6`. – Tim Biegeleisen Oct 23 '15 at 14:56

0 Answers0