0

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!

  • 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 Jun 6 '18 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 Jun 6 '18 at 17:41
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_"

  • I have a backup from my sql, so I will test now, thank you! One more question? What do you mean this: "Don't forget to replace your Database Prefix with "wp_"" --> where need replace this prefix? The code above not enough? – Galgóczi Levente Jun 6 '18 at 17:56
  • check your wp-config.php file for the database prefix. Coz when you install wordpress there an option to set prefix. Unless you changed its by default "wp_" – Sabbir Hasan Jun 6 '18 at 18:00
  • if you used auto installer like softaculous to install wordpress then its no longer the default prefix. – Sabbir Hasan Jun 6 '18 at 18:01
  • was an syntacs error, but just missing $ from wpdb->prepare ... I corrected, but still not working, I get an error from /wp-includes/wp-db.php on line 1222 – Galgóczi Levente Jun 6 '18 at 18:24
  • whats the error says – Sabbir Hasan Jun 6 '18 at 18:26

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.