I have imported 100 thousands of custom Taxonomy terms in my WordPress database. I have imported Country -> State -> Cities hierarchically. By this my database tables terms(20 mb) and taxonomy terms(15mb) have been this much bigger that my whole website and admin panel has gone down and can't even load a single page of my website. I have also tried elasticPress, Jetpack which could not help me for my this issue. Basically I want to import at least 5 countries with its states and cities into my database, so user can choose it's location with a drill down drop-down approach. Could you please help me anyone? Can I cache this or how can I do this without affecting the performance of mysql database? I can give you more information about it. Please ask me anything. Thank you. PS: I have been hosting my this website on CloudWays

2 Answers

Rick James On

WP uses the EAV (Entity-Attribute-Value) schema model. This is rather clumsy and it does not scale well past 'thousands'.

There is, however, a partial fix by improving the "meta" schemas in WP: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

The 'real' fix is to design your own schema with columns for attributes, not reaching indirectly to find them.

There is not a real good reason to have Country->State->City be a hierarchy. A flat table with those 3 columns (and maybe some other stuff) is faster and less clumsy. Other Taxonomies may need to be stored hierarchically. Would you like to discuss a specific one.

MySQL 8 has a new feature: CTEs. They make it easier to write queries to traverse hierarchies.

Romel Indemne On

Create a REST API to output your post instead of normal php. It will load your post faster. Take advantage of this Wordpress function register_rest_route()