Being as my website is now nearly 100% dynamic SQL, I rather like to keep things ‘ticking along’, and as optimal as possible.

This stated, my rather open format tends to grate on the tables with revision after revision after revision of the same structure (I’ve edited this entry 12 times, just as an example – A bit much for most of my entries, but I had a few optimizations and HTML, then De-HTML-ifying). This, coupled with a ‘Hey, QuickBlog doesn’t look like it optimizes the table, does it?’ question I received yesterday prompted me to write the following EXAMPLE code.

Note that this assumes an in installation of my QuickBlog software and a properly configured include file which as your database name, database password, and user name. It’s up to you to modify for your needs.

php include(‘myblog.inc’); mysql_connect($dbhostname, $dbusername, $dbpasswd); mysql_select_db(”$dbbasename”); echo ‘‘; echo ‘‘; echo “

Optimizing Database: $dbname.

“; $result = mysql_query=(“show tables”); $num_results = mysql_num_rows($result); print “There are $num_results tables.
“; for ($i = 0; $i < $num_results; $i++) { $myrow = mysql_fetch_array($result); $result2 = mysql_db_query(’$dbusername’, ‘OPTIMIZE TABLE $myrow⁰‘); print “ · $myrow⁰ optimized.
“; } echo ‘‘; ?>

All this does is obtain your user information, then chew through your database, happily optimizing each table it comes across – Note the lack of actual testing $result2 for failure. As I said, this is merely an EXAMPLE to be used. I would generally suggest some form of plaintext authorization for script execution, IP via globals, or whatnot. :)

I’ll probably end up adding this as a single-click ‘Optimize Table’ for QuickBlog in a future release, only coded specific to QuickBlog’ s table structure. phpBB2 has a rather neat ‘auto-prune’ feature for it’s own database which checks entries based upon date. I might consider modifying this style and moving some elder data to subtables based upon year and month and recoding the current archive function, but that’s currently beyond the spec of this document. ;)