WordPress database indexing?

Question

MySQL

I implement data filtering on WordPress, filtering can be by many custom taxonomies and the value of arbitrary fields that each post can have. Accordingly, you can choose any combination of them.
Requesting this case through WP_Query does everything as expected, but the speed is not very good with 50K+ records.
Now I use a separate table for the filter, where I store for each record all its parameters used for filtering, in this case, the filtering is based on its values ​​and, when forming the page, I drag all the record data from standard tables.
In this case, everything is chic and works quickly. But there is a minus, The initial formation of this table takes a fair amount of time, due to all sorts of not fast WordPress hooks and actions, but this is still half the trouble, the main problem is also a long update of it when batch changing records, due to the same actions and filters.
Is there some alternative storage solution for fast filtering?
Sphinx will not work in this case, the solution should work on an ordinary expensive shared.

Answer 1

сделайте индексы в бд на необходимые поля.Вес базы конечно подрастет, но выборка будет идти быстрее. Хотя на мой взгляд пытаться добиться от wp скорости - это как мастерить ebs в ВАЗ 2101. Интересно, но не стоит ни кому рассказывать.
Как вариант еще можно кешировать запросы, если их вариаций не миллион, то вполне решение.

Answer 2

elasticpress . For true connoisseurs of shared services, Elastic Search is available as a cloud service.

Answer 3

Sphinx won't work in this case.

www.mysql.ru/docs/man/Fulltext_Search.html