CODEWITHSUNDEEP
mysqlwordpresscomposite-index
tklodd
tklodd

Reputation: 1079

How to show composite index in mysql?

I'm writing a WordPress plugin, and I want to check to make sure a composite index is placed on the postmeta.post_id and postmeta.meta_key columns together when the plugin is being activated and to warn the user to create this index if it has not been already. Here is what I have for a single index on the postmeta.post_id column:

$meta_post_id_indices = $db->query("
    SHOW INDEX
    FROM {$wpdb->postmeta}
    WHERE column_name = 'post_id'
");
if (!$meta_post_id_indices) {
    echo "You might consider creating an index on the {$wpdb->postmeta}.post_id column.  ALTER TABLE {$wpdb->postmeta} ADD index(post_id);";
}

How do I implement similar code for a combined (post_id, meta_key) index?

Upvotes: 0

Views: 287

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

Here's how I'd do it:

select index_name, group_concat(column_name order by seq_in_index) as columns 
from information_schema.statistics
where (table_schema, table_name) = ('test', 'postmeta') 
group by index_name having columns='post_id,meta_key';

(the schema name 'test' is just an example; you should replace 'test' with the name of your wordpress schema.)

Most SHOW statements in MySQL are really implemented as queries against views in the information_schema anyway, so this is just going direct to the source data, which gives you more flexibility in how you can query it.

Upvotes: 0

Related Questions