WordPress site with many taxonomies causing server overload issues

Other Questions Wordpress
1

My movie streaming site gets around 10k daily visitors but keeps crashing due to CPU hitting 100%. The server specs are solid - multiple Xeon processors with 64GB RAM, so hardware isn’t the problem.

I’m running a WordPress site with tons of taxonomies: 2000 categories for seasons, 12k tags, and 36k custom taxonomies for actors and directors. This setup makes sense for organizing movies and TV shows.

The real issue is super slow database queries that take forever to run. Here’s an example of what’s killing my server:

-- Query taking 8.2 seconds, examining 380k+ rows
SELECT wp_content.ID 
FROM wp_content 
INNER JOIN wp_taxonomy_links 
    ON (wp_content.ID = wp_taxonomy_links.content_id)
WHERE 1=1 
  AND (wp_taxonomy_links.taxonomy_term_id IN (2850,
    3101,3102,3103,3104,3105,3106,3107,3108,3109,3110,
    3111,3112,3113,3114,3115,3116,3117,3118,3119,3120,
    3121,3122,3123,3124,3125,3126,3127,3128,3129,3130,
    -- hundreds more IDs follow
    41250,41289))

My table structure looks like this:

CREATE TABLE wp_content (
  ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  content_type int(2) NOT NULL,
  author_id bigint(20) unsigned NOT NULL DEFAULT '0',
  publish_date datetime NOT NULL,
  title text NOT NULL,
  content longtext NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'publish',
  show_name varchar(140) NOT NULL,
  season_num int(2) NOT NULL,
  episode_num int(5) NOT NULL,
  series_id int(10) NOT NULL,
  PRIMARY KEY (ID),
  KEY content_type_status (content_type, status, publish_date),
  KEY series_lookup (series_id),
  KEY season_episode (season_num, episode_num)
);

CREATE TABLE wp_taxonomy_links (
  content_id bigint(20) unsigned NOT NULL,
  taxonomy_term_id bigint(20) unsigned NOT NULL,
  sort_order int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (content_id, taxonomy_term_id),
  KEY taxonomy_lookup (taxonomy_term_id)
);

What can I do to fix these slow queries and stop my server from crashing?

2

sounds like u need 2 optimize your db. try addin a composite index on wp_taxonomy_links for taxonomy_term_id and content_id. itll help with those slow queries. also, consider caching some of that data using redis or memcached to reduce the load. good luck!

3

Your issue is those massive IN clauses with hundreds of IDs - MySQL chokes on them even with good indexes. I hit this exact problem on a big e-commerce site. Break those huge queries into smaller chunks - cap each one at 50-100 taxonomy IDs and merge the results in PHP. Also, denormalize your most common taxonomy combos into a lookup table. For movies, you’re probably running ‘action movies from 2020’ searches constantly - just pre-compute those. And definitely cache those heavy taxonomy queries for 30+ minutes since movie data rarely changes.

4

I’ve dealt with this exact nightmare on my content-heavy WordPress site. You’re not just hitting indexing issues - WordPress fundamentally can’t handle this many taxonomies efficiently. The database tweaks others mentioned will help, but you need Elasticsearch or similar for movie filtering. Let MySQL off the hook entirely for complex taxonomy queries. That query structure screams you’re loading way too much data at once. Add pagination or lazy loading to those taxonomy-heavy pages. Split large taxonomy operations into chunks and cache frequently used combinations with WordPress transients - this alone cut my server load in half. Hardware won’t save you if WordPress processes 380k+ rows every page load.