mysql performance issue

[matchlock]

Thanks for the quick reply!

Which site?

www.matchlockarms.com

How many products do you have?

6800

Have you any idea which link causes it?

I'm not sure if it is on the admin side or customer side. Or what link for that matter. I'm going to poke around a bit to see if i can make it happen.

That query comes from the pagination logic which checks to see how many products fall in the specified category or filter, so that it can determine what "next page" links to show, etc.

It would be helpful to know which filter link or sort link or pagination link you click on to trigger the problem.

Hopefully i can identify and post back shortly.

It would also be helpful to know the entire query, not just the first 100 characters, if possible.

Do you know of a way to show the whole query show show processes or something of the like?

You could try running a REPAIR TABLE and OPTIMIZE TABLE on your products, products_description, and products_to_categories tables just in case something's damaged in them.

I'll try this. Anything to be concerned of when I do so?

[DrByte]

6800 products isn't a lot.
If they're heavily laden with attributes, that does certainly create additional burden, but shouldn't seriously impact displaying a product listing.
A REPAIR or OPTIMIZE will cause a temporary lock on the tables while it does the job, and customers will just experience a brief hang if they click while that lock is in place until the lock is released. If your store had 10x that many products then I'd suggest waiting until a slow time to do it.

The slow-query-log might be needed for you to see the details of the query.
Or a GUI tool like Navicat or SQLYog which can extract more information from the database than phpMyAdmin does in things like SHOW PROCESSES and other things.