On Wed, Oct 26, 2011 at 12:52 AM, Lester Caine <lester@xxxxxxxxxxx> 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
>
>
