Page 1 of 1

mysql_query capacity in terms of items returned...

Posted: Thu Oct 02, 2008 6:49 pm
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

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

Posted: Thu Oct 02, 2008 7:25 pm
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 )

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

Posted: Fri Oct 03, 2008 11:49 am
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

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

Posted: Fri Oct 03, 2008 12:15 pm
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.

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

Posted: Fri Oct 03, 2008 5:25 pm
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 ??? :)

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

Posted: Fri Oct 03, 2008 5:41 pm
by josh
http://www.doctrine-project.org supports paging queries

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

Posted: Fri Oct 03, 2008 9:17 pm
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.

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

Posted: Sat Oct 04, 2008 8:52 am
by peyro
Thank you very much califdon for the tutorial page... I will follow the common practice as you suggested :)

peyro