How to Add an Index to Plugin Database table

by

You can execute arbitrary SQL statements with wpdb::query(), including Data Definition Statements, e.g.

function
create_index ()
{
    global $wpdb ;

    $sql = "CREATE INDEX my_index ON {$wpdb->prefix}my_table (my_column)" ;

    $wpdb->query ($sql) ;

    return ;
}

Note: Because $wpdb->query() can execute arbitrary SQL, if the statement you pass to it contains ANY user input, then you should use wpdb::prepare() to protect against SQL Injection attacks.

But this raises the question: how did you create your plugin-specific tables? “Manually” or programmatically? If programmatically, did you not use $wpdb->query()? If you did it “manually”, then you really should create the tables (and their indexes) upon plugin activation.

See the excellent answer to this other WPSE question for how to hook into plugin activation (and/or deactivation and uninstall) to do things like create private tables.

Leave a Comment