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:
> Perhaps if you could tell us what you are trying to do we could suggest 
> ways for doing it better?


I have 100000 **products** loaded into the shopping cart.

FRom there is slow to bring up the shopping cart.


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!
Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!
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