List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:September 15 2011 7:09pm
Subject:Re: optimising for 100000 entries
View as plain text  
On Thu, September 15, 2011 11:27, The Doctor wrote:
> On Thu, Sep 15, 2011 at 08:55:16AM -0400, Shawn Green (MySQL) wrote:
>> On 9/14/2011 15:26, The Doctor wrote:
>>> On Wed, Sep 14, 2011 at 09:49:34PM +0530, Ananda Kumar wrote:
>>>> So,
>>>> You want to have 100,000 buttons for 100,000 entries or just have one
>>>> filter
>>>> column, which allows you to specify any type of "WHERE CONDITION"
>>>>
>>>> regards
>>>> anandkl
>>>>
>>>> On Wed, Sep 14, 2011 at 7:17 PM, Arthur
>>>> Fuller<fuller.artful@stripped>wrote:
>>>>
>>>>> Forgive  my bluntness, but IMO it is silly to attempt to retrieve a
>>>>> 100,000
>>>>> rows, except for reporting purposes, and in that case, said reports
>>>>> ought to
>>>>> run against a replica, not the OLTP instance.
>>>>>
>>>>> Far better, IMO, is to present (in the UI) an alphabet as buttons,
>>>>> plus a
>>>>> textbox for refinements. The alphabet buttons cause the recordSource
>>>>> to
>>>>> change to something like "SELECT * FROM Clients WHERE ClientName
> LIKE
>>>>> 'A*'.
>>>>> Click the B button and the RecordSource changes to "SELECT * FROM
>>>>> Clients
>>>>> WHERE ClientName LIKE 'B*'. IMO, such an interface gives the user
> all
>>>>> the
>>>>> power she needs, and costs the system as little as possible.
>>>>>
>>>>> To accomplish this, all you need is a sproc that accepts one
>>>>> parameter,
>>>>> that being the letter corresponding to the letter-button the user
>>>>> pressed.
>>>>>
>>>>> I have implemented exactly this solution on a table with only half
>>>>> the
>>>>> number of rows you cite, but it works beautifully and it is quick as
>>>>> lightning.
>>>>>
>>>>> HTH,
>>>>> Arthur
>>>
>>> Arthur,
>>>
>>> this is exactly what comes to mind.
>>>
>>> I am wonder what needs to be adjusted in osCommerce for this to work.
>>>
>>
>> I am still confused by your question.  Most modern databases (even those
>> that are not client-server capable) don't even break a sweat at handling
>> only 100K rows of data. It is the types of queries you write and how
>> much
>> data you are attempting to move at any one time that are the most likely
>> reasons for poor performance.
>>
>> Please clarify what you want to fix when you say "optimise MySQL for
>> 100000
>> entires".  Even with the minimal settings on a low-powered laptop, I
>> would
>> have no qualms about loading any version of MySQL produced in the last
>> 10
>> years with a million rows of data and using it for personal research. Of
>> course, there are things I could (and would) configure to help MySQL use
>> it's host system more efficiently. All of that is covered in the chapter
>> in
>> the operating manual called "Optimization". Pick the link below that
>> matches the version you are using for more information:
>> http://dev.mysql.com/doc/refman/5.0/en/optimization.html
>> http://dev.mysql.com/doc/refman/5.1/en/optimization.html
>> http://dev.mysql.com/doc/refman/5.5/en/optimization.html
>>
>> Perhaps if you could tell us what you are trying to do we could suggest
>> ways for doing it better?
>>
>
> Clarification:
>
> I have 100000 **products** loaded into the shopping cart.
>
> FRom there is slow to bring up the shopping cart.
>
> Check http://www.nk.ca/~aboo/racing/osc4/catalog/
>
> to see what is happening.
OK, this is a catalog not a shopping cart.
I think you need to turn off buffering so that the web server sends stuff
out as it gets it.  It appears to be waiting for the full page to be built
before it sends anything.  Its a php system so a PHP expert should chime
in with how to do this.

I suspect the category system which is listing the number of products on
the left is taking a lot of the time.
How do you link your products to your categories?  I suspect you will need
to add a column in the category table to list the number of products so
you are not doing a select count(*) Products under Category X each time.
If your select to list the categories is also doing the count then you
might want to try separating that out (two select's).  Without your table
structure I'm shooting in the dark a bit here.

Just a few thoughts.  Luck.

The shopping cart is not the issue, the category list on the left is, I
think.


-------

William R. Mussatto, Senior Systems Engineer
http://www.csz.com
Ph. 909-920-9154 ext 101
Direct: 909-962-8547
______________________________________________________________________
CONFIDENTIALITY NOTICE:
This communication, including attachments, is for the exclusive use of the
person or entity to which it is addressed and may contain confidential,
proprietary and/or privileged information. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this by mistake, please contact the sender
immediately.

Thread
optimising for 100000 entriesThe Doctor14 Sep
  • Re: optimising for 100000 entriesAnanda Kumar14 Sep
    • Re: optimising for 100000 entriesArthur Fuller14 Sep
      • Re: optimising for 100000 entriesAnanda Kumar14 Sep
        • Re: optimising for 100000 entriesArthur Fuller14 Sep
        • Re: optimising for 100000 entriesThe Doctor14 Sep
Re: optimising for 100000 entriesThe Doctor15 Sep
  • Re: optimising for 100000 entriesWm Mussatto15 Sep
  • Re: optimising for 100000 entriesmos16 Sep