Martin Norland wrote:
Hatem wrote:
Fetching 10 rows is much faster than 1000 ! Fourat your code is optimized just keep it as it :) just keep your code away from adodb, pear db, and such abstraction if you want speed ! you don't need to talk about optimisation with 2 queries.
Regards, Hatem
Depends on the DB, in many cases the times are so similar as to not be worthwhile - but yes, I agree - limits are definitely worthwhile.
Don't know what you are using ;)
Transferring 1000 records is always going to take time, when you only need 10 to be displayed.
Run the query without the limit, this gives you the count - don't actually fetch the rows. Now run the same query, with the limit. If your database is worth anything (most any is), it has this query cached and it takes negligible extra time, and you don't have to spend time 'skipping' ahead X rows. If your database interface functions support 'skipping' ahead - use that instead.
The trick with any transactional database is to maintain the most used counts in a second table, and manage those counts with triggers, so you only need a single record read to access them. The counts will always be valid for your view of the database then.
Obviously, for page 1 of a paginated list, this performs worse than just running the single query. But if you get to page 99, you'll likely find this is faster. Feel free to do your own tests, many factors can change all of these findings, and it's best to match them to suit your own scenario.
The ADOdb pager only needs to know how many pages to indicate in the navigate bar, and how many records to download. In theory this can be very fast, and only slows down where a database engine does not support a simple limited read. pgsql driver in ADOdb supports LIMIT so it's only the calculation of COUNT(*) that needs replacing with a faster pre-calculated count.
-- Lester Caine ----------------------------- L.S.Caine Electronic Services
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
