Tommy Pham wrote:
It turns out the issue was actually in the pagination... I'm reworking the
> whole thing and stream lining it... But in the pagination that I found on
> the internet it used a "SELECT COUNT(*) WHERE state='{$state}'"; and the
> COUNT was killing the time... Once that was removed, I was displaying
> records faster then I could imagine... So it's off to pagination land to fix
> it! And possibly redo the entire thing!
>
If you're encountering performance issues while doing SELECT COUNT(*), it
sounds like you have serious disk IO performance issue. Is the DB on RAID
subsystem? If not, why not? If so, what level? Also, what type of HDDs?
For something like this, it should be RAID 10 with HDDs spinning at least
7200RPM, 10,000+ RPM recommended, connected to a good RAID controller, like
3ware's. Also, the controller should be in slot PCI-X or, preferably,
PCI-e.
What a load of twoddle ...
SELECT COUNT(*) is a problem on any fully transactional database, since it has
to be generated from the currently active view of the data.
Rather than trying to get the database engine access every record faster, the
correct action is to either avoid the count altogether, or more practically
maintain a separate table with useful counts that have been generated from the
committed data.
Jason, it is not unreasonable that an initial view will be displaying unfiltered
data, so you just need to restrict the number of records displayed. As you have
found out, telling the user how many more records there are is the real problem,
so if it's not important ... don't, but if it's useful to know, then keep a
'cache' of counts that link to your initial filter options. Once you are down to
a few thousand records, then a SELECT COUNT(*) may be appropriate ;) Where it
becomes a problem is when there the user can set up a more complex filter that
the cache does not cover ...
--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
