PHP DB - RE: [PHP-DB] Re: paginating : optimising queries

Author

RE: [PHP-DB] Re: paginating : optimising queries

Hatem

2005-03-22, 3:56 pm

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

--- "Patel, Aman" <Aman.Patel@STJUDE.ORG> wrote:
> I've run in to this situation before. And it turns
> out that the extra
> pre-"count" query is not worth it. So the steps
> become:
>
> 1) Do your main query and fetch the results.
> 2) Use the count of results obtained in step 1.
> 3) Calculate pagination numbers.
> 4) Use array_slice to limit the results to a page
> window calculated in
> step 3.
>
> Hope this helps.
>
> - Aman Patel, Sys Admin / Database / Web Developer,
> International
> Outreach x4076
>
> queries
> paginate
> page 2 ...) i
> show in every page
> the page number X.
> all the
> user is :
> column like 'value%'
> LIMIT OFFSET
> for one
> pages i have by
> page.
> tables... it's a
> people do, can you do
> http://www.php.net/unsub.php
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Découvrez le nouveau Yahoo! Mail : 250 Mo d'espace de stockage pour vos mails !
Créez votre Yahoo! Mail sur http://fr.mail.yahoo.com/

Martin Norland

2005-03-22, 3:56 pm

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.

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.

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.

cheers,
--
- Martin Norland, Sys Admin / Database / Web Developer, International
Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.

Lester Caine

2005-03-22, 3:56 pm

Martin Norland wrote:

> Hatem wrote:
>
>
> 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

Martin Norland

2005-03-22, 3:56 pm

Lester Caine wrote:
[snip]
> Don't know what you are using ;)
> Transferring 1000 records is always going to take time, when you only
> need 10 to be displayed.
[snip]
I wasn't referring to transferring the record, only running the query.
[snip]
>
>
> 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.
[snip]
This only works for known queries - anything that is filtered or
otherwise limited won't benefit from this - but you still need to spend
time maintaining the count. I'm speaking in a general, global,
automated statement creation sense - you can't just wrap a count(*)
around any given sql query - and in many cases, even when you can - it
still ends up computing just as much information, so either way it
calculates all the rows that would be returned.
[snip]
>
> 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.
[snip]
I've been talking about using LIMIT the whole time, I'm not sure where
you're going with this - I was merely further explaining what was likely
obvious anyway. Naturally when you pull in * ordered by an indexed
column you gain speed with limits, but when you start putting conditions
on things, the queries have to run across (many|all) the rows.

I try to speak in general terms on this list, not everyone is using
ADOdb or mysql (though I often slip into speaking about mysql since
people either use it, are familiar with it, or at the very least - any
standard sql engine will support the same features)

My statements were always related to using pagination in a general sense
- e.g. a class you create for pagination, that can paginate any manner
of query cleanly. If you're dealing solely with hard-coded /
hand-crafted queries, you can obviously build your pagination to fit.

cheers,
--
- Martin Norland, Sys Admin / Database / Web Developer, International
Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.

Martin Norland

2005-03-22, 3:56 pm

On a side note, who's sick of seeing the word optimizing misspelled in
the title? :)

Ah, the things we do to keep a thread clean.

--
- Martin Norland, Sys Admin / Database / Web Developer, International
Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.

Sponsored Links