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.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. 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
