RE: [PEAR] DB - Query(limit) vs. LimitQuery() in MySQL

> From: Tommy Ipsen > Sent: Tuesday, September 28, 2004 13:33 > > Peter Bowyer wrote: > > >> Does LimitQuery take advantage of MySQLs features when > executing the > >> query? > > > > Yes, as does the PostgreSQL driver. Some of the others > emulate this. > > Does this mean that LimitQuery doesn't extract *all* the records from > the table and *then* extracts the necessary ones but rather > translates > the use of LimitQuery to a MySQL-standard query (SELECT ... > LIMIT a,b) > hence taking advantage of MySQLs LIMIT option? > In case of MySQL LimitQuery appends LIMIT a,b. In case of other databases (for example Oracle) the query is wrapped so it can emulate limiting. > > Use limitQuery if you care about portability. It's > designed for this task. > > If you want pure speed then using query() and writing the > limit SQL will > > be fractionally faster, but in that case why not forget > using PEAR::DB > > and use the native mysql functions instead? > > Of course I'm interested in portability but the costs can also be too > big - for instance if the LimitQuery() actually extracted 1.000.000+ > records from a table in order to show 10 records to the user it would > probably be quite time-consuming - but if LimitQuery() is > "intelligent" > enough to take advantage of MySQL's LIMIT-syntax then > everything is okay! > > I found the following in the man-pages for the DB-package: > (http://pear.php.net/manual/en/package.database.db.db-common.l > imitquery.php) > > "Depending on the database you will not really get more speed compared > to query(). The advantage of limitQuery() is the deleting of unneeded > rows in the resultset, as early as possible. So this can decrease memory > usage." > > - I can't figure out exactly what this means - will the method extract > all records from a table and THEN delete the unnecessary ones OR will > the method just extract the necessary records - when using MySQL as DBMS? > > Cheers, Tommy Ipsen Most databases first get all data from the request, and then select which rows to return depending on your limit. I believe the same happens in MySQL, so if you have a big table and want stuff from the end, it might actually be faster to reverse-sort the table and then limit it. I have seen them do this in the phpBB code so I guess it's better for performance. Using limit is still faster than getting all data and then filtering what you need in your php, as with LIMIT not all rows have to be send from the database to the webserver, which can save a lot of time in case of big resultsets. Hope that helps, Herman -- PEAR General Mailing List (http://pear.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php