How to Add an Index to Plugin Database table

Question

I've created some additional tables for a plugin I'm developing and need to add indexes to these tables.

What's the WordPress way to do this?

Using dbDelta() doesn't seem to be working, and I'm not seeing any error in the logs.

Answer 1

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.

Answer 2

Using dbDelta, on top of a PRIMARY KEY, you can include the word KEY to create an index for other columns:

You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.

Example from schema.php in core:

CREATE TABLE $wpdb->termmeta (
  meta_id bigint(20) unsigned NOT NULL auto_increment,
  term_id bigint(20) unsigned NOT NULL default '0',
  meta_key varchar(255) default NULL,
  meta_value longtext,
  PRIMARY KEY  (meta_id),
  KEY term_id (term_id),
  KEY meta_key (meta_key($max_index_length))
) $charset_collate;

Source: codex - Creating Tables with Plugins