On Wed, Oct 26, 2011 at 12:52 AM, Lester Caine <les...@lsces.co.uk> wrote:

> 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 ...
I wonder ... The real question is what's the purpose of the DB?  Is it for
OLAP or OLTP? ;)
As for dealing with DB having millions of rows, you're crossing over into
DBA area.

> 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<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<http://www.firebirdsql.org/index.php>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to