SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,761
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Questions about LIMIT

    Is it correct that when you use LIMIT at the end of a query, that MySQL is still returning the *entire* Recordset (e.g. 250 records) but only displaying what is defined by LIMIT (e.g. 20 records)?

    Sincerely,


    Debbie

  2. #2
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Right, let's say you want to grab the latest 5 blog posts out of 1,000. You would order them by date and LIMIT them to 5.

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,761
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by eruna View Post
    Right, let's say you want to grab the latest 5 blog posts out of 1,000. You would order them by date and LIMIT them to 5.
    When you do Pagination, do you really want to return *all* records but just display *some* records?

    For example, if there were indeed 1,000 records in the result-set, would I want to bring over all 1,000 records and then just display 5?!

    It almost seems like you'd want your query to *just* return the records you want to display...

    Follow me?

    Sincerely,


    Debbie

  4. #4
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,805
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    You would limit by a start and finish point based on variables passed to the query.

    When you give the query 2 variables
    eg
    Code:
    SELECT 
    	  field1
    	, field2
    FROM
    	mytable
    LIMIT 5, 10
    would return row 6-15 from your results.

    So to control the start point using a variable would be a simple matter of passing the STARTPOINT via a $_GET link

    Code:
    SELECT 
    	  field1
    	, field2
    FROM
    	mytable
    LIMIT $start, 10
    So the first number is the offset of rows to return and the second is the maximum rows you want - in this case 10

    Your link would look like
    Code:
    <a href="page.php?start=0">1</a>
    <a href="page.php?start=10">2</a>
    <a href="page.php?start=20">3</a>
    <a href="page.php?start=30">4</a>
    <a href="page.php?start=40">5</a>
    Make sense?
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,761
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    SpikeZ,

    Wow, that was more than I asked for, but still a good thing!


    Quote Originally Posted by spikeZ View Post
    You would limit by a start and finish point based on variables passed to the query.

    When you give the query 2 variables
    eg
    Code:
    SELECT 
    	  field1
    	, field2
    FROM
    	mytable
    LIMIT 5, 10
    would return row 6-15 from your results.
    Originally I thought the two numbers in LIMIT where the Start and End points...

    Where are you getting Row 6?

    If the "offset" is "5", shouldn't it start at Row 5?

    Sincerely,


    Debbie

  6. #6
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Would I want to bring over all 1,000 records and then just display 5?!
    It's better to let the SQL do what it does best and let the PHP do what it does best. (You can substitute SQL/PHP with whatever you are using.)
    You could call all the data and have the PHP terminate the loop after five rows, and it would work. Though, you are bringing a larger piece of information to be processed than is necessary. These efficiencies add up when the scale of your application increases and decrease performance.

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Is it correct that when you use LIMIT at the end of a query, that MySQL is still returning the *entire* Recordset (e.g. 250 records) but only displaying what is defined by LIMIT (e.g. 20 records)?
    No.

    LIMIT causes the database to return only the number of records that you specified in LIMIT.

    How many of those records you end up displaying is a matter for your application, your database has no control over what you do with the records it returns in a query.

  8. #8
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,805
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Originally I thought the two numbers in LIMIT where the Start and End points...

    Where are you getting Row 6?

    If the "offset" is "5", shouldn't it start at Row 5?
    Results/arrays start at 0
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  9. #9
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "If the "offset" is "5", shouldn't it start at Row 5?"

    OFFSET is the number of rows that are skipped, so OFFSET 5 will skip the first five records and the first record you will see returned is record six.

    LIMIT 4 OFFSET 7 will return records 8, 9, 10 and 11.

    PS: the syntax of LIMIT x,y is a MySQL-only thing. MySQL also supports the standard of LIMIT x OFFSET y, so use that instead, it makes life easier in the long run.

  10. #10
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,761
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinny42 View Post
    No.

    LIMIT causes the database to return only the number of records that you specified in LIMIT.

    How many of those records you end up displaying is a matter for your application, your database has no control over what you do with the records it returns in a query.
    Incorrect.

    The Manual says...

    http://dev.mysql.com/doc/refman/5.0/...imization.html


    Debbie

  11. #11
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't understand what you think is incorrect about what I said?

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,761
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinny42 View Post
    I don't understand what you think is incorrect about what I said?
    Quote Originally Posted by vinny42 View Post
    Quote Originally Posted by DoubleDee
    Is it correct that when you use LIMIT at the end of a query, that MySQL is still returning the *entire* Recordset (e.g. 250 records) but only displaying what is defined by LIMIT (e.g. 20 records)?
    No.

    LIMIT causes the database to return only the number of records that you specified in LIMIT.
    I interpreted what you said to be that MySQL only returns (i.e. "processes") the records you specify in LIMIT, which isn't true.

    From the Manual...
    If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found.
    That means that if you have 100 records in your table, and MySQL has to do a filesort on a query with a LIMIT(0, 10), it might have to touch 80 of the records to get them sorted in the correct order *before* it returns the 10 records you are requesting.

    That is consistent with one of my original questions.

    So it is not true to say that LIMIT(0, 10) only touches 10 records, because MySQL might have to do quite a bit more work. Then again, it might get lucky and be able to return 10 records straight up?!

    Sincerely,


    Debbie

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I interpreted what you said to be that MySQL only returns (i.e. "processes") the records you specify in LIMIT, which isn't true.
    actually, it is true

    "returns" means "returns" -- you ask for 10, you get only 10

    what it has to do before returning those 10 is a separate issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,761
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    actually, it is true

    "returns" means "returns" -- you ask for 10, you get only 10

    what it has to do before returning those 10 is a separate issue
    I thought that one of the digs against MySQL (vs Oracle) is that it is not as efficient, and that it basically grabs the whole recordset, loads it into the server's memory, sorts things, AND THEN sends over what is in LIMIT. (Although I'm not sure how else you'd do it more efficiently?!)

    Sincerely,


    Debbie

  15. #15
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, I see where the confusion comes from :-)

    I use the term "returns" for the data that comes back from the query to the application that sent the query to the database (hence: returns ).
    Records that the database reads internally are "fetched".

    I thought that one of the digs against MySQL (vs Oracle) is that it is not as efficient, and that it basically grabs the whole recordset, loads it into the server's memory, sorts things, AND THEN sends over what is in LIMIT. (Although I'm not sure how else you'd do it more efficiently?!)
    Strange : ) MySQL has long been known for stopping the query processing as soon as it can determine that the LIMIT requirement has been met.

    However, if there is an ORDER BY clause then the database needs to have the records in that order before it can determine where your LIMIT and OFFSET take effect. If there is no index then the only thing the database can do is to process all records, sort them, apply the LIMIT and OFFSET to the result and send whatever is left back to the application.
    But that's true for any database because as you said; how else can you do it?

  16. #16
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,761
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinny42 View Post
    Ah, I see where the confusion comes from :-)

    I use the term "returns" for the data that comes back from the query to the application that sent the query to the database (hence: returns ).
    Records that the database reads internally are "fetched".
    Okay.


    Quote Originally Posted by vinny42 View Post
    Strange : ) MySQL has long been known for stopping the query processing as soon as it can determine that the LIMIT requirement has been met.

    However, if there is an ORDER BY clause then the database needs to have the records in that order before it can determine where your LIMIT and OFFSET take effect. If there is no index then the only thing the database can do is to process all records, sort them, apply the LIMIT and OFFSET to the result and send whatever is left back to the application.
    But that's true for any database because as you said; how else can you do it?
    Just me getting confused!

    Sincerely,


    Debbie


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •