mysql_query capacity in terms of items returned...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
peyro
Forum Newbie
Posts: 4
Joined: Thu Oct 02, 2008 6:42 pm

mysql_query capacity in terms of items returned...

Post by peyro »

Hello,

I have a question regarding doing php querys to mysql with myslq_query function. Basically I want to understand how much fields can be processed in a single query? I mean, if an assigment like the following is issue:
$result=mysql_query....
what is the limit of entries that $result will handle??? Basically I need to do a query over a database and show them in a webpage, in groups of 20 per page, and enable a "NEXT" link to see further results, like a query in GOOGLE (at a lower scale :) )...

THanks in advance for any help forum...
peyro
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: mysql_query capacity in terms of items returned...

Post by josh »

that's called pagination, and rather then get into the details of how the DBMS handles the resource the best advice I can give you is don't worry about there being a limit for practical reasons. Queries can return 100s of millions of rows or potentially more, its also DBMS specific ( older versions of mysql had limits of 4GB per database table that have long been overcome )
peyro
Forum Newbie
Posts: 4
Joined: Thu Oct 02, 2008 6:42 pm

Re: mysql_query capacity in terms of items returned...

Post by peyro »

Thanks jshpro for the advise. So basically pagination process takes care of it, and at php script level shouldnt be an issue (in practical terms I am talking about 20,000 - 50,000 records searches maximum)...

Thnaks again..
peyro
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mysql_query capacity in terms of items returned...

Post by califdon »

But do understand that pagination is something you must take care of in your PHP script. If you might have as many as 50,000 rows in a result set, you might want to re-think how you want to handle it. At 20 per page, that's a potential for 2,500 pages--obviously far more than anyone could ever page through. Indeed, I would consider anything over a few hundred potential results to require a different indexing method, or if that's not feasible, perhaps use a LIMIT clause in your SQL and when the returned row_count exceeds your limit, add a user message that says something like "...further results truncated" or something. Of course, it all depends on the application and its requirements.
peyro
Forum Newbie
Posts: 4
Joined: Thu Oct 02, 2008 6:42 pm

Re: mysql_query capacity in terms of items returned...

Post by peyro »

this is a good point califdon,

What I was thinking on is to use mysql_data_seek() to move over the result array each time a "NEXT" is click (display around 100 results per page). Maybe storing a variable in a user $_SESSION variable that keeps where I were before the "NEXT". The drawback is that several queries will be performed.

Thanks for the response,
peyro
PD: is there a more efficient way to do this ??? :)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: mysql_query capacity in terms of items returned...

Post by josh »

http://www.doctrine-project.org supports paging queries
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mysql_query capacity in terms of items returned...

Post by califdon »

peyro wrote:this is a good point califdon,

What I was thinking on is to use mysql_data_seek() to move over the result array each time a "NEXT" is click (display around 100 results per page). Maybe storing a variable in a user $_SESSION variable that keeps where I were before the "NEXT". The drawback is that several queries will be performed.

Thanks for the response,
peyro
PD: is there a more efficient way to do this ??? :)
Read http://www.tonymarston.net/php-mysql/pagination.html for a straightforward tutorial on pagination. I have never used mysql_data_seek(). The common practice is to use the LIMIT clause, as discussed in this tutorial.
peyro
Forum Newbie
Posts: 4
Joined: Thu Oct 02, 2008 6:42 pm

Re: mysql_query capacity in terms of items returned...

Post by peyro »

Thank you very much califdon for the tutorial page... I will follow the common practice as you suggested :)

peyro
Post Reply