WordPress Database Table Optimizer
The WordPress Database Table Optimizer plugin will automatically make sure that your WordPress MySQL database tables are always optimized. All you need to do is up load the ft_db_optimize folder to your WordPress plugins folder, activate the plugin and it will do the rest.
I’ve also included an admin page located at Manage > Optimize Database. This page will allow you to confirm your WordPress MySQL database tables are optimized, show you the number of rows per table, and the size of the table.
36 Responses to “WordPress Database Table Optimizer”
Rasmus Grouleff Says:
October 18th, 2008 at 2:01 pm
What sort of optimizations are this plugin performing on the database?
Marion Dorsett Says:
October 18th, 2008 at 2:52 pm
The optimization is to reclaim the unused space and to defragment the data file. Much the same as deframenting your harddrive.
Marion Dorsett Says:
October 19th, 2008 at 3:26 pm
Yes, it’s as safe as WordPress is. The plugin creates a string to pass through the tables listed in the $wpdb->tables array. The new string is MySQL escsaped before the query is executed.
If an exploit were found in this plugin, it would more than likely be an exploit for WordPress vs. a direct exploit of this plugin.
David Potter Says:
October 20th, 2008 at 1:04 pm
Very nice plugin. It looks like it doesn’t show all the tables, though. It doesn’t appear to include tables added by plugins (e.g. In Series, cross-references, etc.). It’s not clear from the code, though, why it’s not picking them up. Any ideas?
Also, it would be helpful if you could put a link on the manage page to this page or to the page on wordpress.org. I’m trying to encourage plugin and theme authors to do this as it is a great way to foster community and to get help and provide feedback simply.
Marion Dorsett Says:
October 20th, 2008 at 10:13 pm
David,
Thanks for the feedback and nice catch on the missing plugin tables. I originally had the plugin pull the tables from the data base where they were LIKE `$wpdb->prefix` and then changed it when I realized the tables were already stored in the $wpdb->tables object. What I failed to notice was that only the native WP tables get listed in that object. I’ve already addressed that issue, I’ll release the update in a few hours after I review the changes, and confirm all is working properly.
AainaA Says:
October 20th, 2008 at 11:40 pm
Marion,
This is something we’ve been waiting for – will this work with 2.7 beta or solely with 2.6.* ? Would you reckon seeing this as part of WP in the near future?
Thank you for sharing,
AA
Marion Dorsett Says:
October 20th, 2008 at 11:56 pm
AA,
Provided that they $wpdb class still works the same in the 2.7 beta, this plugin should still work. Since they’re updating the interface, the output on the admin page will probably be a bit off.
As far as seeing this as part of WordPress in the future, I really don’t know, but my first impression is `No.`, There’s been ample time to include automatic MySQL optimization, and they’ve not done it yet.
You’re welcome and thank you for your input.
Marion
David Potter Says:
October 21st, 2008 at 3:15 am
Hi Marion,
Thanks for the update. When I installed it on my web site, it showed the two links you added – thanks 
– but nothing else. In case it makes a difference, I’m running on IIS6 on Windows Server 2003, although I don’t imagine it will. Let me know if you need anything else from me to track it down.
Thanks,
David
Max Says:
October 21st, 2008 at 5:03 am
Marion,
I’ve installed your plugin. Anyway, on the Optimiza Database page no table are shown. For security reasons, I’ve changed the default table prefix to a different one. maybe this plugin does expect that the tables have the default prefix? (so, right now, I think it’s doing nothing on my site)
Regards,
Max
Marion Dorsett Says:
October 21st, 2008 at 7:59 am
Max,
You’re right, I missed one of the direct calls I made to the `wp_` tables, I’ll fix this later today, and re-upload,
David,
The problem that Max experienced is probably the same issue you’re having as well.
Thank you all for the feedback.
Max Says:
October 21st, 2008 at 4:47 pm
Thanks Marion!
Now it works correctly. I’m happy to help when possible.
Ciao,
Max
SRTNCO Says:
October 21st, 2008 at 9:33 pm
Am I missing something? I just d/l the file again and it does not appear different than the one I had. And I to changed my prefix….the tables appear on the page but they are all at 0K in size.
SRTNCO Says:
October 21st, 2008 at 9:41 pm
BTW – I didn’t mean to be rude thank you so much for sharing your work.
Marion Dorsett Says:
October 21st, 2008 at 10:00 pm
SRTNCO,
No, actually you’re not missing anything. Version 1.1 and 1.1.1 are identical now. I changed the 1.1 files and committed the changes to the repository, then realized that w/o changing the version no, the WordPress wouldn’t register the fixes I made, and so depending on when you downloaded the plugin, yes, you may not see a difference between the two.
BTW – No offense taken, and you’re welcome.
rohin Says:
October 21st, 2008 at 10:32 pm
in lament terms for non-mysql experts how much of a performance boost can we expect from this , also is it 100% compatible with other plugins.
Marion Dorsett Says:
October 21st, 2008 at 11:00 pm
Rohin,
Performance will vary based on the size of your database, and the capabilities of your server, and how ofter you edit, or delete information from your blog. I can for see that anyone who has a severely fragmented database may see a longer delay on the page load just after they activate the plugin, however, after the initial activation, page load should remain with in consistent levels.
The default WordPress theme provides page load times that are commented out in the footer.php file. You could copy that code in to your theme (if it’s not there already), and judge the time it takes for a page to load. Deactivate the plugin for a while, let the free space build up and then see how it does.
Barring any function naming conflicts, yes, this plugin should be 100% compatible with any other WordPress plugin because it’s primary and only function is to optimize the database. Even if another plugin has already optimized the MySQL tables, this plugin will simply finish quicker.
Worse case scenario, you can Activate & Deactivate this plugin as needed. I know it’s easier & faster for me to access my blog admin than my PHPMyAdmin install. One of the primary reasons I wrote the plugin in the first place.
Hope that helps.
SRTNCO Says:
October 21st, 2008 at 11:11 pm
Ok then I have the most current file but I still have the issue of all the tables being 0B. Any ideas? Thanks again!
Marion Dorsett Says:
October 21st, 2008 at 11:36 pm
The column farthest to the right should always report `0.00 B`. This means that the MySQL table is optimized and all free space has been reclaimed. The second to last column reports the actual size of the database table, and the column that precedes it, shows the rows contained with in that table.
This information can be helpful to determine what tables may be adding unnecessary bloat to your blog, but you’ll have to be the judge of that.
SRTNCO Says:
October 21st, 2008 at 11:54 pm
I understand. I guess I should be more clear…everything is 0…rows, size, and overhead. It’s like the script sees the tables but not the data within them.
Marion Dorsett Says:
October 22nd, 2008 at 7:46 pm
SRNTCO,
Is your database a typical MySQL database? From what I’ve been reading about your issue, if your database is not using the MyISAM or InnODB engine, the only thing that would be reported is the table name, and the rest of the information would return NULL. The function to return the data size in this plugin of course would return a zero value.
At this point I’d have to guess that you’re database is using the `NDBCLUSTER` engine, which is the only thing I can find that would even be close to explaining the issue your having.
Marion Dorsett Says:
October 23rd, 2008 at 5:57 pm
STRNCO,
Can you send me a link to a page on your server where I can see the results of phpinfo(); I moderate all comments, so if you just post it here, I’ll visit the link and delete the comment so it’s not broadcast publicly.
SRTNCO Says:
October 23rd, 2008 at 9:32 pm
I was digging around and noticed something that may help track this down. As I mentioned, I have renamed my prefix from the default. However, I also have tables in the SAME database that have the defaults (i guess from a previous install i did). I am afraid to delete them because I don’t know if something is still using them. I am wondering though if those are the tables your script is seeing.
SRTNCO Says:
October 23rd, 2008 at 9:48 pm
Sorry to spam your comments 
More info:
Before I send you on a goose chase with the tables I mentioned above here is a little more info…none of those are 0B either so regardless which prefix it is grabbing it should not be showing 0B.
Alberto Rodriguez Says:
October 27th, 2008 at 10:29 am
Hi Marion,
Excellent plugin: Neat and effective. The way code should be.
By the way: I’ve been looking through the code and noticed that the plugin is checking the database every single hit (am I correct?). Since I use WP-supercache, this would happen every while in a once but I’m still concerned that If a get a load of traffic (which happens once every 2 weeks), this could create a bottleneck. So, for the time being, I’m keeping this plugin for a “per use” basis instead as “automatic”: I activated once every two days (I use to do it manually every week or so).
I consider my site to be small (we receive an average of 2.5K visits per day with a peak of 16K eventually), but I’ve learned (in the hard way) that I should keep the queries o the minimum. Maybe sites with heavy traffic would need to optimize the table really often, but if you have a cheap hosting and a small website that can be done as low as once per week.
Could it be a function to define some “Schedule”? I mean, maybe the plugin would try to check/optimize tables every 1 hour / 3 hours / 6 hours / 24 hours/days? That would significantly reduce the load on the server and still will keep up in shape the DB.
For the rest, dude, excellent site! I’ll keep on visiting to get more advises.
Regards/AR
Berrie Pelser Says:
October 30th, 2008 at 4:08 am
Very nice plug-in, thx! Maybe a small enhancement tip: can you add all the table sizes together so we also can see the total size of the WP database?
Kevin Says:
November 17th, 2008 at 1:49 am
Great Idea but when I installed this plugin it really dragged the whole server down.
I have a big site of over 6000 posts and some 3000 tags and it is the only thing I can put my finger on.
I used wp-tuner to discover the optimiser was trying to run all the time.
Maybe it just doesn’t run well with nextgen gallery or another plugin but has changed and wp-tuner is pointing at this plugin
Marion Dorsett Says:
January 8th, 2009 at 12:22 am
SRTNCO,
In answer to your question “What happened???”, I picked a bad time to start this little project. Work got very busy, and recently we had a new addition to our family, and he’s been consuming what little time I had.
So… now that things are leveling out at work, the holidays are over, and a few family issues that are now resolved, the new baby will still consumes a lot of my evenings, but I’ll be able to return to my nightly programming projects.
As for the other comments that were posted, Thank you, I skimmed through some, and I’ll work on addressing the issues that were submitted.
To all of you, thanks for the feedback, it’s nice to have real input so that I can focus on real issues that need resolved. I appreciate all of you for your honest comments, praises and criticisms.
Having said all of that I’m off to make this plugin more efficient.
Forgive my absence.
Marion Dorsett Says:
January 8th, 2009 at 5:28 am
Alberto,
Thanks for the feedback. I almost have a working version that will set a rest interval before it’s allowed to execute again. Currently I’m running it at the 24 hr mark. After I have the code working properly, I’ll work on letting people change it to a desired interval that suites their site.
Marion Dorsett Says:
January 8th, 2009 at 5:31 am
Berrie,
Seems like I should have thought of that as well, but shouldn’t be too hard to add a running total. I think you already understand though that it would calculate only the WP tables, not the entire database.
Thanks for the feedback.
Marion Dorsett Says:
January 8th, 2009 at 5:36 am
Kevin,
Thanks for throwing me the bone. I didn’t know about the wp-tuner plugin, but I installed it before making my recent changes, and I think you’ll (all) be pleased with the results. I had hoped to release it tonight, but I ran into some issues trying to update to WP 2.7, so it will probably be sometime this weekend when the code is available.
I’m still running on 2.6.3, but I assume other than the look of the admin page, it should still function properly.
Thanks again for your input.
AndyII Says:
March 16th, 2009 at 4:08 pm
Hi Pete, lovely plugin, nice tool to have but I have a technical issue
Technical issue: Configuration appears correct, yet WP Tuner could not be loaded before wp-db.php, so SAVEQUERIES is not defined
is it due to running version 2.1.3?
Marion Dorsett Says:
March 17th, 2009 at 11:26 am
I didn’t test with 2.1.3, so it’s very possible that the version could be the issues. It could also be the order in which the plugins are loading. I would think if you deactivate the WP Tuner and then reactivate it, it may solve the problem as well, but I’m guessing at that.
October 17th, 2008 at 8:10 am
SWEET! You the man. No more overhead on my SQL!