I also found this extra info that I think would help…
Why do you think modifying WordPress core table is a good idea? – Krzysiek Dróżdż♦ Jun 12 ’15 at 4:21 I really don’t think it’s a good idea but a necessary one if running wordpress with the amount of posts and combined with the limitations of mysql not having a descending index function. Those file sorts caused by the order by operations are a deal breaker for us in regards to site performance. – Ranknoodle Jun 15 ’15 at 2:34 But these operations are slow since you’re doing it wrong. In some projects we had similar issue, but came to very different solution, that didn’t modify core tables. We’ve created our own table and used it as indexing/search table. So every slow query was searching only based on this one table (no joins needed). (And we had much more data, AFAIR) – Krzysiek Dróżdż♦ Jun 15 ’15 at 5:06 Hi KRZYSIEK can you explain a little more on indexing search tables that you created? For example the slow query outline in the original question, I would create a table to store the post ID,reverse_post_id,post_type etc and only query against that? – Ranknoodle Jun 15 ’15 at 16:31 Send me an e-mail, I’ll try to elaborate on that method. – Krzysiek Dróżdż♦ Jun 15 ’15 at 16:34
But no idea on the method he used.