Running a simple SELECT on an optimised WordPress database takes 20 seconds, as it returns 62,000 records. Does anyone have a suggestion on how to improve these timings?
My code:
global $wpdb;
$entries_sql = $wpdb->prepare("SELECT post_id FROM $wpdb->postmeta WHERE meta_key = '%s' AND meta_value='%s'", 'sht_ranking_id', $ranking_id);
$entry_ids = $wpdb->get_results($entries_sql, ARRAY_N);
(I've also tried running the raw SQL using PHPMyAdmin and it's no quicker.)
A result set with 62K rows is large, even when it's only one column (post_id in your case). It's going to take time to retrieve it even if MySQL works optimally. It's always wise only to retrieve the rows you need.
That being said, you can try adding an index on the wp_postmeta table. It may optimize the MySQL aspects of your query.
ALTER TABLE wp_postmeta ADD KEY meta_key_value (meta_key(90), meta_value(90), post_id);
Please, use phpmyadmin to run
EXPLAIN SELECT post_id FROM whatever your query says....
before and after you add that key. It would be helpful if you posted the results of those EXPLAINs by editing your question.