9

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.

4

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.

  • Thanks for answering this question even though it's 5 months old. I ended up doing this but wasn't sure if it was the "right way". Will be a big help the next time I try to do this. – Milk Mar 8 '17 at 23:31
  • @Milk My pleasure. I figured you'd either already solved your problem or had given up :-) I answered it (even tho 5mos after you asked) to help others who have the same problem and search WPSE and come across your question. – Paul 'Sparrow Hawk' Biron Mar 8 '17 at 23:37
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.