Stuff4Toys:
They seem to believe that if I parse through the slow query logs, and determine which table and field is causing the issue and ad an index for that individual field, it will help the issue.Well, there's a half-ounce of truth there, but only if you're dealing with tables that weren't indexed in the first place.
That's why I asked you to analyze the structural differences and report back on what you found to be wrong with indexes vs a clean install. That would reveal which default indexes are missing and need to be put back.
What I was intending to suggest AFTER you do that would be to manually delete and recreate all the indexes. But, that's time-consuming and not really useful unless several indexes are indeed damaged and not getting fixed by a database-wide repair operation. And, the slow-query-log output should expose tables that have large numbers of records and maybe failing indexes, and thus those would be the ones to rebuild. There's no point rebuilding indexes on all tables since many are not used anywhere near as much as others.
Recreating all the indexes by hand is a cumbersome process, and very server-intensive on large tables. Automating it could bring a server to a crawl if not done carefully. But doing it by hand manually allows enough time between setting up for the changes, that the server catches up and doesn't slow everybody down badly.
Stuff4Toys:
He explained database fragmentation and recommended a more regular repair and optimize routine.
?? Question: can the repair and optimization of the database be performed remotely via browser instead of going to phpMyAdmin to do it?
In the current version of Zen Cart there's an Optimize Tables option in the Admin->Tools->Store Manager screen, which loops thru all the tables and optimizes them.
Also, various ZC admin screens already do table optimizing whenever records are deleted from products and categories, etc, to minimize fragmentation.
Addons, however, probably don't go that far, so no promises about any of those.
Stuff4Toys:
He also pointed out a few tables that had excessive overhead, like the whos_online and sessions tables.
?? Question: Can these tables be purged from time to time??Sure. You can truncate those tables anytime you wanna kick everyone off your store.
Stuff4Toys:
Now I start the process of parsing through the slow query logs and see if I can help by adding indexes.Be careful there. Don't go adding indexes willy-nilly. There's a science to creating smart indexes. Creating too many needlessly will slow things down too. A clean install of the current version of ZC already has those optimized pretty well.
Stuff4Toys:
So I started the daunting task of reviewing the slow_query_log and found it littered with entries like this:
[code]# Fri Sep 9 05:49:08 2011
Query_time: 1.300991 Lock_time: 0.000094 Rows_sent: 1 Rows_examined: 3
...
1.3 seconds doesn't sound like a long time, but when I only get every 300th of a clock tick to process, it takes FOREVER.Actually, 1.3 seconds is a HUGE amount of time for a table that has only 3 rows to examine, and only 1 row to return. That's telling you the MySQL CPU activity seems to be taking way longer than it should when given the data it's got to work with.
ANY table with only 3 rows in it, even without any indexes, should return its data with lightning speed.
Stuff4Toys:
Do I need to drop the banners table and recreate it? think this table is corrupt on each domain?If it's only got 3 records in it, then that's unlikely to resolve the problem.
However, this is what I'd do. Take it in stages, and test each one:
a) REPAIR the table first. Then test speed.
b) Then delete and recreate the indexes. (write down the indexes on the table now, delete them, then recreate them). You might test after deleting, and then again after recreating.
c) Then if there's still a problem, delete the whole table and recreate it. Then test. Then import the records back into it, and test again.
Of course, do a backup of the table before you do anything.
