Skip to content

Instantly share code, notes, and snippets.

@buagern

buagern/mysql-auto-add-index.php

Created April 7, 2017 19:14
Show Gist options
  • Save buagern/e1282b585cff19e91d0653a63cd3c8c8 to your computer and use it in GitHub Desktop.
Save buagern/e1282b585cff19e91d0653a63cd3c8c8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. buagern created this gist Apr 7, 2017.
    49 changes: 49 additions & 0 deletions mysql-auto-add-index.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,49 @@
    <?php
    $dbname = 'test';

    if (!$link = mysqli_connect('localhost', 'root', '', $dbname)) {
    echo 'Could not connect to mysql';
    exit;
    }

    $sql = "SHOW TABLES FROM $dbname";
    $result = mysqli_query($link, $sql);

    if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysqli_error($link);
    exit;
    }

    while ($row = mysqli_fetch_row($result)) {
    echo "Table: {$row[0]}\n";
    $colno = 0;
    $colsql = "SHOW COLUMNS FROM {$row[0]}";
    $colres = mysqli_query($link, $colsql);

    if (!$colres) {
    echo "DB Error, could not list columns\n";
    echo 'MySQL Error: ' . mysqli_error($link);
    exit;
    }

    while ($crow = mysqli_fetch_row($colres)) {
    if ($colno > 0 && preg_match('/^((is_(.+))|((created|updated|deleted)_(at|by))|((.+)_id))$/', $crow[0])) {
    $altsql = "ALTER TABLE `{$row[0]}` ADD INDEX(`{$crow[0]}`)";
    $altres = mysqli_query($link, $altsql);

    if (!$altres) {
    echo "DB Error, could not add column index\n";
    echo 'MySQL Error: ' . mysqli_error($link);
    exit;
    }

    echo "Column: {$crow[0]}\n";
    }
    $colno++;
    }

    mysqli_free_result($colres);
    }

    mysqli_free_result($result);