List:General Discussion« Previous MessageNext Message »
From:The Doctor Date:September 15 2011 6:27pm
Subject:Re: optimising for 100000 entries
View as plain text  
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.

> -- 
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN

-- 
Member - Liberal International	This is doctor@stripped Ici doctor@stripped
God, Queen and country! Never Satan President Republic! Beware AntiChrist rising! 
https://www.fullyfollow.me/rootnl2k
Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!
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