I currently have a review website running on WordPress. The website has grown and performance is now and issue. I currently store all the meta data associated with a post/review in the wp_meta table which offers no optimisation for joins between data types or on different tables as far as I can see (e.g everything is stored as LONGTEXT).
I need to redesign the db and wanted to know which would be the best approach. The most frequent queries are to filter data / sort it by post rating / or filter if the post is features each query happens within its own category.
Joins will occur between the category/tag table and the new meta_data table. The category/tag tables are indexed based on the ID of the associated category/tag.
My question is I wanted to add an index on the review_rating value in the new meta_data table, this will be joined with the category/tag table to filter down the results. How would MySQL perform when joining two indexes in this way… i.e. would it need to rebuild the index or does it just filter the two tables based on the existing index… And is this the best approach or is there a more suited way to perform this action..
Oh and I’m using InnoDB
Read more here: Best db design to optimize MySQL joins on large datasets