MySQL determining if there are more rows "left over" when using LIMIT.

[redarrow]

Hey all,

If I use a query like: "SELECT * FROM mytable WHERE <condition> LIMIT 0,10"

What's the best method to determine if there more rows available?
Obviously if less than 10 rows are returned I know that there are no more. But if 10 are returned I can't tell if there are any more of if there are only 10...

So far the only solution I can think of is to always set LIMIT to one more than I need and just ignore the extra row if it exists (it's existence confirms that there are more rows than the amount I need).

Now the only problem with this is it seems like a waste, requesting a record which doesn't really get used. I know in small databases this is a non issue, but I'd really like to know if there is a "correct" way of doing it..

Sorry if this question seems a bit pointless, seeing as I do actually have a solution
I just don't want to get into the habit of using my solution if there is a better one available.

Thanks.

 

[LazerFazer]

Check out this page for a solution to your problem.

 

[hof]

Generally you would add SQL_CALC_FOUND_ROWS to your SELECT statement: SELECT SQL_CALC_FOUND_ROWS * FROM mytable. Then invoke SELECT FOUND_ROWS() after. That will return the total row count. It comes with a performance overhead though, as the query won't complete when the LIMIT is met.

With large databases and performance critical applications I make use of triggers on table inserts and deletes to increase or decrease a stored count field.

However, if you only need to know whether there are additional rows and are not interested in the total row count, your solution is actually rather elegant and will perform well.

 

[redarrow]

Thanks guys

I did not know about the "SQL_CALC_FOUND_ROWS" feature .. very useful..

To be honest I did not think I'd need to know how many rows there are in total - I've been trying to implement a "next page" button in my application.
But now that I think of it knowing how many rows would allow me to calculate how many pages and be able to implement a "jump to page" feature, which is much more useful.

So definitely going to be testing that one.

Thanks again

 

[Light]

Thanks for the awesome info. I will definitely put this to good use!