Lo and behold, a couple tables were identified as fragmented, even though I have a weekly optimization script that checks and repairs any fragmentation.
Just in case there was some sort of anomaly that would bite me in the butt, I decided to run a check to identify the fragmented tables and manually repair those specifically. I found this post by Sean Hull on the DatabaseJournal very helpful!
To quote the author:
"MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient."
The process:
0. Login as root on the Mysql server
mysql -u root -p ************
1. Identify fragmented tablesTake note of the tables, or perhaps select into an outfile if you so desire. This may come in handy if you want to script or automate the identification and repair of the fragmented tables.
select table_schema, table_name, data_free, engine
from information_schema.tables where table_schema
not in ('information_schema', 'mysql') and data_free > 0;
2. Repair the table fragmentation
This could be scripted, but was manually executed as I only had about 4 fragmented tables.
optimize table tablename1;
3. Rerun Step 1 to ensure the fragmentation has been resolved.
-noveck
