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.art...@gmail.com>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 doc...@nl2k.ab.ca Ici doc...@nl2k.ab.ca 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! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
