PHP / MySQL Database Optimization Function

By  on  

After frequent record deletion from your MySQL database tables, your tables can acquire overhead. Overhead is empty space left inside the database table due to the deletions. A great way to speed up your MySQL database, not to mention keep it compact, is to use a simple PHP function to optimize your database tables:

/*  OPTIMIZE ALL TABLES  */
function optimize_database($DATABASE_LINK) {
	$result = mysql_query('SHOW TABLES', $DATABASE_LINK) or die('Cannot get tables');
 	while($table = mysql_fetch_row($result)) {
		mysql_query('OPTIMIZE TABLE '.$table[0], $DATABASE_LINK) or die('Cannot optimize '.$table[0]);
	}
}

I use the $DATABASE_LINK variable to keep my connection throughout my PHP script. Obviously it's not required, so you can modify the above function if you don't keep that variable.

Recent Features

  • By
    Camera and Video Control with HTML5

    Client-side APIs on mobile and desktop devices are quickly providing the same APIs.  Of course our mobile devices got access to some of these APIs first, but those APIs are slowly making their way to the desktop.  One of those APIs is the getUserMedia API...

  • By
    An Interview with Eric Meyer

    Your early CSS books were instrumental in pushing my love for front end technologies. What was it about CSS that you fell in love with and drove you to write about it? At first blush, it was the simplicity of it as compared to the table-and-spacer...

Incredible Demos

  • By
    Multiple File Upload Input

    More often than not, I find myself wanting to upload more than one file at a time.  Having to use multiple "file" INPUT elements is annoying, slow, and inefficient.  And if I hate them, I can't imagine how annoyed my users would be.  Luckily Safari, Chrome...

  • By
    Flashy FAQs Using MooTools Sliders

    I often qualify a great website by one that pay attention to detail and makes all of the "little things" seem as though much time was spent on them. Let's face it -- FAQs are as boring as they come. That is, until you...

Discussion

  1. Dear sir,
    where we use this function?
    i need help, my hosting SQl is overhead
    i use this function in my function Class php file and upload it
    but i see it still overhead
    let me know how to use it

    reply back

    • With a PHP script, you can have it as a stand alone (with connection info), or you can put it as part of your code, as I have.

  2. Great script. I was looking for a way to compact a MySQL database, and this is perfect. Thanks.

  3. trimd

    This article is like my ass,

    its big shit

  4. Freed

    @trimd: hei trimd, you so stupid!

    if you want fast compact you can make a program with delphi or VB6 to compact it. i make a application to compact my mysql database 370MB (7years period) become 90MB. Look more carefully trimd.

    make a little application to compact it, use Delphi or VB6
    don’t look others samples
    grow trimd !

    for more explanation go to mysql site.
    or dev forum for VB

    try to become great programmer.
    not like junior!

  5. Thanks, for the scripts because so far I have only seen two, the one for backing up the tables and this one above. They all look to be great and I’m use them in the my project.

  6. sohail

    Thanks. I grabbed this code and created a WP plugin. Thanks again. :-)

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!