database - How to create index (sql) to a meta_key?

admin管理员组

文章数量:1122826

I am new in programming, and the sql is absolutely stranger for me. I create today a new meta_key on my multisite, but when I use this in a query, my site will be horrible slow... I read (here: /), that need for me creating a sql index for the meta_key, and this will doing incredibly faster my site... So here this sql command:

CREATE INDEX wp_postmeta_csstricks ON wp_postmeta (meta_key)

If my meta_key is _my_first_meta, I can use this code?:

CREATE INDEX wp_postmeta_my_first_meta ON wp_postmeta (_my_first_meta)

And where can I use this? In phpmyadmin? Please, If you can help me, write a reply for this, any help is usefull! I really dont find a simple and helpfull total tutorial for this on the net!

I am new in programming, and the sql is absolutely stranger for me. I create today a new meta_key on my multisite, but when I use this in a query, my site will be horrible slow... I read (here: https://css-tricks.com/finding-and-fixing-slow-wordpress-database-queries/), that need for me creating a sql index for the meta_key, and this will doing incredibly faster my site... So here this sql command:

CREATE INDEX wp_postmeta_csstricks ON wp_postmeta (meta_key)

If my meta_key is _my_first_meta, I can use this code?:

CREATE INDEX wp_postmeta_my_first_meta ON wp_postmeta (_my_first_meta)

And where can I use this? In phpmyadmin? Please, If you can help me, write a reply for this, any help is usefull! I really dont find a simple and helpfull total tutorial for this on the net!

• database
• mysql
• sql

Share Improve this question asked Jun 6, 2018 at 16:51 Galgóczi LeventeGalgóczi Levente 18933 silver badges1717 bronze badges 2

• Post meta is optimised for "Find me all meta values with this key where I already know the post ID" or "find me all meta for post X". It's not optimised or structured at all for searches, and an index will not help much at all. That's why taxonomies and taxonomy tables exist, which are orders of magnitude faster. The taxonomy tables were built for searching or posts that have X or posts that have Y. The problem is not how to add an index, but that you're storing the data you want to query for in post meta. Post meta is for when you already know which post you want and you need details – Tom J Nowell ♦ Commented Jun 6, 2018 at 17:40
• Also, if core ever changes that table your efforts will break and you'll need to redo the index – Tom J Nowell ♦ Commented Jun 6, 2018 at 17:41

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I'm not going to tell you about what will happen. I'll here give you an example on how you can run that piece of sql without phpMyadmin. Add this to your functions.php for temporary purpose. Not to forget about removing this after one run of your website.

$mymeta="_my_first_meta";
$wpdb->query(
   $wpdb->prepare("CREATE INDEX wp_postmeta_my_first_meta ON wp_postmeta (%s)",$mymeta)
);

Don't forget to replace your Database Prefix with "wp_"

本文标签： databaseHow to create index (sql) to a metakey