MySQL Query Performance Issues

I've got no mysql help for you, but are you seriously expecting people to use a 400 item drop-down list box? You may want to re-think your interface before you spend time optimizing the sql queries.

sounds more like a problem with the software rather then MySQL, I really dont think any amount of tweaking will fix that if the software is doing stupid queries.

Yes, I do expect them to when they are looking for their family name. Since they are doing it on our present e-commerce site, and we're the largest supplier of rental kilts in the US, I don't see why they wouldn't do the same when we switch to our new site.

But thank you both for your input so far - the next version of Zencart is, hopefully, supposed to handle larger amounts of attributes better. I was hoping there would be a way to manage for the time being through dealing with the mysqld variables.

(I'm not a MySQL expert. I have more experience with MS SQL Server.)

You could try to figure which one of the queries takes the most time and add some indexes to improve the query's performance.

@marv:

(I'm not a MySQL expert. I have more experience with MS SQL Server.)

You could try to figure which one of the queries takes the most time and add some indexes to improve the query's performance.

I really dont think it's the length of queries, it's just that there's so many of them. Indexes really wont help in this matter.

@melancthon:

Yes, I do expect them to when they are looking for their family name. Since they are doing it on our present e-commerce site, and we're the largest supplier of rental kilts in the US, I don't see why they wouldn't do the same when we switch to our new site.

And you can't categorize the products by family name so you wont have 1 product with 500 attributes? Hopefully you'll realize there comes a point when a redesign is better then continuing to support something that was implemented wrong in the first place.

my $.02

So I should have 500+ different kilts? I don't think that solves or simplifies anything for the customer.

I wouldn't use that my.conf I posted years ago – it was meant for a VERY small memory footprint.

In my opinion, performance tuning is as much an art as a science -- there's no one-size-fits-all solution that's going to solve everyone's problems. It takes some detective work to identify each bottleneck, and then the know-how (read: google) to fix that bottleneck. It takes some patience first time around, but eventually you'll have some intuition or insight as to where to look the next time around…

I suggest you start by looking at your memory variables inside your my.conf. Larger buffers and whatnot can decrease time spent performing disk scans during joins, lookups, and about a million other things. Check out how well your tables' indexing is being used by using EXPLAIN query.

How about caching this stuff inside the application?

-Chris

One more thing:

If it takes 10,000 queries to build a page, you're doing something wrong. :) SQL freaking rocks, and I'm almost positive you'd be able to reduce that to 1 query (or at most a handful).

-Chris

As an update, I have been migrated to a Xen host, and that has reduced query times quite a bit, I suppose simply because there is more power being thrown at my particular process. I will accept that for the time being :)