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

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

You would limit by a start and finish point based on variables passed to the query.

When you give the query 2 variables
eg


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


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



<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?

SpikeZ,

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

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

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.

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.

Results/arrays start at 0

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

Incorrect.

The Manual says…

http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

Debbie

I don’t understand what you think is incorrect about what I said?

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

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

Ah, I see where the confusion comes from :slight_smile:

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 :slight_smile: ).
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?

Okay.

Just me getting confused!

Sincerely,

Debbie