I have been working on fixing a piece of code for a customer that has been very slow for them.

I have made that fix, and thats all lovely, but I noticed something odd in the paging.

When the query is executed, it does NOT contain a LIMIT keyword. Then, based on the per-page value and the current page the viewer is on, the result reader is told to fetch from the index of the Record at (perPage * currentPage - 1) to the Record at (perPage * currentPage) - 1, and the total number of Records the result has are used to determine the total number of pages.

So, are there any benefits doing it this way as opposed to using the LIMIT keyword, and a separate COUNT(*) query (possibly only done once per search context)?

  • So....MySQL...or....? Nov 12 '14 at 16:11
  • @MarkSinkinson On the system in question, it was Oracle, and although I know that each Database will have its own optimizations, I was wondering about it on a more database agnostic level
    – topherg
    Nov 12 '14 at 16:53
  • well, either way, I am wondering what the performance comparison would be between using a record count function on the result object (from select * from ...) and just using select count(*) from ...
    – topherg
    Nov 12 '14 at 16:59
  • @a_horse_with_no_name ah, fair enough. Link deleted Nov 12 '14 at 17:32
  • @cgoddard Oracle didn't have LIMIT before 12c came out (you had to use a subquery and rownum), so that probably explains the lack of it :)
    – Philᵀᴹ
    Nov 12 '14 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.