Results 1 to 1 of 1

Thread: How to Optimize a MySQL Database

  1. #1
    Tom's Avatar
    Tom
    Tom is offline Net Builder
    Join Date
    Dec 2008
    Posts
    171
    Thanks
    12
    Thanked 111 Times in 57 Posts

    How to Optimize a MySQL Database

    This PHP script optimizes MySQL databases for best performance.

    Edit the MYSQL_USERNAME and MYSQL_PASSWORD to the correct values for your database and then upload the file to your host with the name optimizedb.php.

    To optimize all of the databases in your MySQL server, call the script like this:
    http://www.example.com/optimizedb.php?dbname=all
    To optimize a single database, call the script like this:
    http://www.example.com/optimizedb.php?dbname=NAME_OF_YOUR_DATABASE
    Code:
    <?php
    
    # Please setup your MySQL Config before continuing
    # It is found right here at the top of this file...
    
    #########################################################
    # MYSQL CONFIG
    #########################################################
    global $db_user, $db_password, $db, $db_host;
    $db_user = "MYSQL_USERNAME";
    $db_password = "MYSQL_PASSWORD";
    $db = "server";
    $db_host = "localhost";
    
    
    
    # No additional includes are needed to run this script!
    # All functions & config are in this file
    # CONFIG SHOWN ABOVE
    # FUNCTIONS SHOWN AT END OF SCRIPT
    
    
    #########################################################
    
    $dbname = $_GET["dbname"]; # Try not to edit this, ok? :)
    $repair = $_GET["repair"]; # Make this 1 if you want to repair instead
    
    global $DoWhat;
    
    if ($repair==1) {
    $DoWhat="REPAIR";
    } ELSE {
    $DoWhat="OPTIMIZE";
    }
    
    #########################################################
    # PERMISSIONS required for script to work
    #########################################################
    
    # Must have access to MySQL, using the login info above
    #
    # Must be able to:  Show Databases, Show Tables from php
    #
    # Must be able to:  $DoWhat TABLE $db.$table;
    #
    # Must be able to:  Repair Table $db.$table;
    
    #########################################################
    
    
    #################
    # USAGE
    #################
    
    # TO OPTIMIZE YOUR ENTIRE SERVER (the $db_host) above
        # optimizedb.php?dbname=all
    
    # TO OPTIMIZE A SINGLE DATABASE
        # optimizedb.php?dbname=mydatabase
    
    # TO OPTIMIZE A SINGLE TABLE    
        # optimizedb.php?dbname=mydatabase.mytable
    
    #:)
    
    
    
    ############################
    # ACTUAL SCRIPT
    ############################
    
    if (strstr($dbname,".")) {
    
    # Optimize Single Table Only
    $optresult = SmartQuery("ARR $DoWhat TABLE $dbname;");
    $tbname = $optresult[0];
    $tbstatus = $optresult[3];
    
    echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>";
    
    
    } ELSE {
    
    if ($dbname=="") {
    echo "No usage commands provided!<br><br>
    optimizedb.php usage commands:<br><br>
    
    # TO OPTIMIZE YOUR ENTIRE SERVER
        # optimizedb.php?dbname=all
    
    # TO OPTIMIZE A SINGLE DATABASE
        # optimizedb.php?dbname=mydatabase
    
    # TO OPTIMIZE A SINGLE TABLE    
        # optimizedb.php?dbname=mydatabase.mytable
    
    ";
    
    } ELSE {
    
    if ($dbname=="all") {
    
    # Optimize Entire Server
    
    $dbs = SmartQuery("BIGARR show databases;");
    
    echo "<b>Optimizing " . count($dbs) . " databases: DO NOT STOP YOUR SERVER!!!<br>" . ForceEcho() . ForceEcho() . ForceEcho() . ForceEcho() . ForceEcho() . "<br><br></b>" .  ForceEcho() . ForceEcho() . ForceEcho();
    
    for ($y=0; $y < count($dbs); $y++) {
    $dbname=$dbs[$y][0];
    
    $optlocations = OptimizeDatabase($dbname);
                
                for ($x=0; $x < count($optlocations); $x++) {
                    $tbname = $optlocations[$x][0][0];
                    $tbstatus = $optlocations[$x][0][3];
                    if ($tbname=="") {
                    } ELSE {
                        if ($tbstatus=="") {
                        } ELSE {
                            if (strstr($tbstatus,"doesn't exist")) {
                            } ELSE {
                            echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>" . ForceEcho() . ForceEcho() . ForceEcho();
                            }
                        }
                    }
                }
    }
    
    } ELSE {
    
    # Optimize One Database
    
    $optlocations = OptimizeDatabase($dbname);
                
                for ($x=0; $x < count($optlocations); $x++) {
                    $tbname = $optlocations[$x][0][0];
                    $tbstatus = $optlocations[$x][0][3];
                    echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>";
                }
    
    }
    }
    }
    
    ###################################################################
    # OPTIMIZE DATABASE FUNCTION
    ###################################################################
    # Don't edit this!
    # Don't edit this!
    # Don't edit this!
    # Don't edit this!
    ###################################################################
    # Also shows some usage of the SmartQuery Multi-Dimensional Arrays
    ###################################################################
    
    function OptimizeDatabase($dbname) {
    # Gets all of the tables in $dbname, and optimizes them..
    global $DoWhat;
    $tables = SmartQuery("BIGARR SHOW TABLES IN $dbname;");
    $optresult = '';
        for ($x=0; $x < count($tables); $x++) {
            $table = $tables[$x][0];
            $optresult[$x] = SmartQuery("BIGARR $DoWhat TABLE $dbname.$table;");
        }
    
    return $optresult; # returns format like this: $optresult[$x][0][3]  thats the status...
        
    }
    
    
    #################################################
    # SMART QUERY - MULTIDIMENSIONAL ARRAY SUPPORT
    #################################################
    
    # usage:
    
    #    to return a count: $recordcount = SmartQuery("SELECT count(*) from database.table;");
    
    #    to return a single info: $record = SmartQuery("SELECT FirstName from database.table WHERE LastName='jones';");
    
    #    to return 1 row as an array: $record = SmartQuery("ARR SELECT * from database.table WHERE LastName='jones';");
        # $somethingCol1 = $record[0];
        # $somethingCol2 = $record[1];
        
    #    to return all rows as a multi-dimensional arrays: $record = SmartQuery("BIGARR SELECT * from database.table WHERE LastName='jones';");
        # $somethingRow1Col1 = $record[0][0];
        # $somethingRow1Col2 = $record[0][1];
        # $somethingRow2Col1 = $record[1][0];
        # $somethingRow2Col2 = $record[1][1];
    
    
    
    # Don't edit this!
    # Don't edit this!
    # Don't edit this!
    # Don't edit this!
    # Don't edit this!
    
    
    function SmartQuery ($QueryString) {
    
    global $db, $db_host, $db_user, $db_password;
    $con = mysql_connect($db_host,$db_user,$db_password);
    if (!$con) {
    die("MySQL Database Connection Problem: " . mysql_error() . "\n *");
    exit;
    }
    
    if (is_array($QueryString)) {
    
        # Array of queries...
        
        for ($x=0; $x < count($QueryString)+1; $x++) {
        
        $arrquery = $QueryString[$x]; # The Query ($x)
            
            if ($arrquery=="") {
            # No query
            } ELSE {
            
            # has query
            
            if (strtolower(substr($arrquery,0,3))=="arr") {
            # Returns a normal ARR of 1 row
            
            $arrquery=trim(substr($arrquery,3));
            $returnarray=true;
            $returnbig=false;
            
            } ELSE {
            
                if (strtolower(substr($arrquery,0,6))=="bigarr") {
                # Returns a big ARR of all rows
                $arrquery=trim(substr($arrquery,6));
                $returnarray=true;
                $returnbig=true;
                } ELSE {
                $returnarray=false;
                $returnbig=false;
                }
            }
            
            
            
            $query = $arrquery;
            
            mysql_select_db("$db");
            
            $mysql_result = mysql_query($query, $con);
            
                if ($returnbig==true) {
                # Output large array - special
            
                    $ret[0] = mysql_fetch_row($mysql_result); # set first row
                    
                    for ($y=1; $y < mysql_num_rows($mysql_result); $y++) {
                    # Multi-dimensional arrays
                    $ret[$y] = mysql_fetch_row($mysql_result);
                    }
                    
                    $buildret[$x]=$ret;
                    
                } ELSE {
                # Normal output
                $ret = mysql_fetch_row($mysql_result);
                if ($returnarray==true) {
                $buildret[$x] = $ret;
                } ELSE {
                $buildret[$x]=$ret[0];
                }
                }
                
                } # for x (array queries
            } # check empty
            
            return $buildret;
            
            
            
    # END OF ARRAY QUERIES
    
    
    } ELSE {
    
    # BEGIN OF SINGLE QUERY
    
            
        # Normal query
            
            if (strtolower(substr($QueryString,0,3))=="arr") {
            # Returns a normal ARR of 1 row
            $QueryString=trim(substr($QueryString,3));
            $returnarray=true;
            $returnbig=false;
            } ELSE {
                if (strtolower(substr($QueryString,0,6))=="bigarr") {
                # Returns a big ARR of all rows
                $QueryString=trim(substr($QueryString,6));
                $returnarray=true;
                $returnbig=true;
                } ELSE {
                $returnarray=false;
                $returnbig=false;
                }
            }
            
            $query = $QueryString;
            mysql_select_db("$db");
            #error_reporting(0);
            $mysql_result = mysql_query($query, $con);
                if ($returnbig==true) {
                # Output large array - special
            
                    $ret[0] = mysql_fetch_row($mysql_result); # set first row
                    
                    for ($x=1; $x < mysql_num_rows($mysql_result); $x++) {
                    $ret[$x] = mysql_fetch_row($mysql_result);
                    }
                    
                    return $ret;
                    
                } ELSE {
                # Normal output
                $ret = mysql_fetch_row($mysql_result);
                if ($returnarray==true) {
                return $ret;
                } ELSE {
                return $ret[0];
                }
                }
    }
    }
    
    
    # Forces the screen to echo some information back, by flooding it with spaces. Do not use this in a loop with 1 million or more, or it could fill your entire screen up to a GB of data.
    
    # use this to force status onto the screen.
    
    # You can edit this :P
    # You can edit this :P
    # You can edit this :P
    # You can edit this :P
    
    
    function ForceEcho() {
    return "                                                    
            
    
                                                                
                                                                
                                                                
    
                                                                
                                                                
                                                                
    
    
    
                                                                
                       .";
    }
    
    
    ?>

  2. Thanked by:

    badtux (31 July, 2009), Charles (23 December, 2008)

Similar Threads

  1. [WTS] US Golf Courses Database (MySQL) – 19,168 Records
    By yangyang in forum Content
    Replies: 0
    Last Post: 22 December, 2010, 15:16 PM
  2. MySQL database update
    By proweb in forum Databases
    Replies: 4
    Last Post: 22 May, 2010, 22:55 PM
  3. Backup of Mysql database using PhpMyAdmin
    By mega in forum Databases
    Replies: 2
    Last Post: 14 May, 2009, 10:06 AM
  4. How to Repair a MySQL Database
    By Will.Spencer in forum Databases
    Replies: 19
    Last Post: 13 January, 2009, 03:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •