Title: Taxonomy Optimization Last modified: April 6, 2022 --- # Taxonomy Optimization * Resolved [Christoph Bratschi](https://wordpress.org/support/users/cbratschi/) * (@cbratschi) * [4 years, 1 month ago](https://wordpress.org/support/topic/taxonomy-optimization/) * Our site uses a custom taxonomy to group pages by content area (e.g. regional and international news). The following query takes about 0.4 s and filters by this custom taxonomy and categories: * ``` SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) WHERE 1=1 AND wp_posts.ID NOT IN (955051,955206,955241,955142,954997,955261,955205,945949,955216,954926,954629,954970,954201,955028,955155,955026,955095,948822,11078,954545,335679,105100) AND ( wp_term_relationships.term_taxonomy_id IN (1,329,461,469,613,1111,1113,1114,1777,1778,1782,1783,1784,1785,1786,1795,1796,1797,1798,1800,1801,2172,2173,2345,2651,2652,2653,2654,2655,2656,2657,2658,2659,2660,2661,2663,2664,2665,2666,2667,2668,2702,2709,2720,2727,2735,2743,2939,3127,6271,6272,8301,11830,20241,23403,55642,78874,95617,101764,101765,103121,162524,162704,172907,178170,196469,226186,232671,238748,257957,257958,310668,316215,336628,336630,336631,388033,421629) AND tt1.term_taxonomy_id IN (461295,461296,461297,461301) ) AND wp_posts.post_type IN ('post', 'page') AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 7 ``` * Due to the size of our tables this query is quite expensive. Every post uses our custom taxonomy and multiple categories. Ideally this query could be done without processing huge amounts of data. * Explain result: * ``` 1 SIMPLE tt1 NULL range PRIMARY,term_taxonomy_id term_taxonomy_id 16 NULL 47731 100.00 Using where; Using index; Using temporary; Using f... 1 SIMPLE wp_posts NULL eq_ref PRIMARY,post_name,post_parent,type_status_date,pos... PRIMARY 8 web.tt1.object_id 1 41.31 Using where 1 SIMPLE wp_term_relationships NULL ref PRIMARY,term_taxonomy_id PRIMARY 8 web.tt1.object_id 6 50.00 Using where; Using index ``` * In this case the returned post will be just a few days old but it gets more complicated in case of pagination. * Any ideas what we could improve here? - This topic was modified 4 years, 1 month ago by [Christoph Bratschi](https://wordpress.org/support/users/cbratschi/). Viewing 5 replies - 1 through 5 (of 5 total) * Plugin Author [OllieJones](https://wordpress.org/support/users/olliejones/) * (@olliejones) * [4 years, 1 month ago](https://wordpress.org/support/topic/taxonomy-optimization/#post-15538121) * Thanks for the interesting example! Looking at this, I have some comments. * — This query is generated by the [WP_Tax_Query class]([https://developer.wordpress.org/reference/classes/wp_tax_query/](https://developer.wordpress.org/reference/classes/wp_tax_query/)); the SQL code generation it uses is quite stylized. So the shape of this query– those long `IN (123, 456, 789)` clauses–comes from waaaay upstream in the logic. — I get a different EXPLAIN than you. But my tables have a bit of fake data in them, and yours have large quantities of real data. — The length of those IN- lists looks like it’s somehow triggering full-index-scans. — WordPress’s indexing of the taxonomy tables is pretty close to optimal. * I wonder if you would run a monitor in the plugin (Tools / Index MySQL / Monitors) and do whatever generates that query a few times, then upload the monitor. The plugin’s monitor feature does EXPLAIN on each different shape of query, so we get a lot of good info. * (Adding indexes is easy. Adding _useful _indexes? Not so much. That’s why we go to the trouble of helping you collect information about query performance — we need actual examples.) - This reply was modified 4 years, 1 month ago by [OllieJones](https://wordpress.org/support/users/olliejones/). - This reply was modified 4 years, 1 month ago by [OllieJones](https://wordpress.org/support/users/olliejones/). * Thread Starter [Christoph Bratschi](https://wordpress.org/support/users/cbratschi/) * (@cbratschi) * [4 years, 1 month ago](https://wordpress.org/support/topic/taxonomy-optimization/#post-15540613) * I will collect some data later today on the production system. We are monitoring all queries in our theme and report everything above 10 s. If there are any problems we know them. These queries are > 90 % of all slow queries and this happens a few times per day. The backend runs 20 parallel PHP instances and push notifications can cause some spikes (we have lots of push subscribers). On the other end LiteSpeed Cache delivers stale data if possible to reduce such situations but they can still occur. * The taxonomy query matches a lot of posts: in case of the categories I would guess > 90 % and the region about 70 %. Before the region data was stored in post_meta fields. Switching to taxonomies only doubled the performance, we expected more. There are enough performance guides available but they often only apply to average instances. If you run into special conditions it’s much harder to optimize the whole environment. I was also looking into clustered databases ( Percona XtraDB cluster) which sustained higher loads but queries perform equally if not a little bit slower due to the cluster management. Also tried NDB but long texts are not compatible. * Thread Starter [Christoph Bratschi](https://wordpress.org/support/users/cbratschi/) * (@cbratschi) * [4 years, 1 month ago](https://wordpress.org/support/topic/taxonomy-optimization/#post-15540662) * Well, I uploaded the saved monitor. * Plugin Author [OllieJones](https://wordpress.org/support/users/olliejones/) * (@olliejones) * [4 years, 1 month ago](https://wordpress.org/support/topic/taxonomy-optimization/#post-15542969) * Thanks. I’ll follow up by email. * Plugin Author [OllieJones](https://wordpress.org/support/users/olliejones/) * (@olliejones) * [3 years, 9 months ago](https://wordpress.org/support/topic/taxonomy-optimization/#post-15960912) * Please don’t hesitate to open another support thread if I can help you further. Viewing 5 replies - 1 through 5 (of 5 total) The topic ‘Taxonomy Optimization’ is closed to new replies. * ![](https://ps.w.org/index-wp-mysql-for-speed/assets/icon-128x128.png?rev=2652667) * [Index WP MySQL For Speed](https://wordpress.org/plugins/index-wp-mysql-for-speed/) * [Frequently Asked Questions](https://wordpress.org/plugins/index-wp-mysql-for-speed/#faq) * [Support Threads](https://wordpress.org/support/plugin/index-wp-mysql-for-speed/) * [Active Topics](https://wordpress.org/support/plugin/index-wp-mysql-for-speed/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/index-wp-mysql-for-speed/unresolved/) * [Reviews](https://wordpress.org/support/plugin/index-wp-mysql-for-speed/reviews/) * 5 replies * 2 participants * Last reply from: [OllieJones](https://wordpress.org/support/users/olliejones/) * Last activity: [3 years, 9 months ago](https://wordpress.org/support/topic/taxonomy-optimization/#post-15960912) * Status: resolved