How to Add Keys on wp_term_relationships and wp_term_taxonomy Table?

  • Resolved jaypabs

    (@jaypabs)


    2 years, 3 months ago

    Since I installed this plugin and use the High-Performance Keys, my site runs faster.

    However, there’s some tables that I always noticed that’s slowing my site. It’s wp_term_relationships and wp_term_taxonomy.

    Can you please help me add keys to this table?

    Here’s a sample query that runs for more than 0.7 seconds based on Query Monitor plugin:

    SELECT SQL_CALC_FOUND_ROWS pn_posts.ID
FROM pn_posts
LEFT JOIN pn_postmeta
ON ( pn_posts.ID = pn_postmeta.post_id
AND pn_postmeta.meta_key = '_whp_hide_on_categories' )
WHERE 1=1
AND ( pn_posts.ID NOT IN (
SELECT object_id
FROM pn_term_relationships
WHERE term_taxonomy_id IN (89143,89281,89282,89283,89284,89285,89286,89287,89288,89289,90831,90832,90833,91299,93202,118371,118372,211068) ) )
AND ( pn_postmeta.post_id IS NULL )
AND ((pn_posts.post_type = 'post'
AND (pn_posts.post_status = 'publish'
OR pn_posts.post_status = 'private')))
GROUP BY pn_posts.ID
ORDER BY pn_posts.post_date DESC
LIMIT 0, 10

    Here’s another query that runs more than 0.5 seconds:

    SELECT SQL_CALC_FOUND_ROWS pn_posts.ID
FROM pn_posts
LEFT JOIN pn_postmeta
ON ( pn_posts.ID = pn_postmeta.post_id
AND pn_postmeta.meta_key = '_whp_hide_on_frontpage' )
WHERE 1=1
AND ( pn_posts.ID NOT IN (
SELECT object_id
FROM pn_term_relationships
WHERE term_taxonomy_id IN (12,43,9453,12875,72861,89143,89149,89150,89151,89266,89267,89268,89269,89270,89271,89272,89273,89274,89275,89276,89277,89278,89279,89280,89281,89282,89283,89284,89285,89286,89287,89288,89289,89870,90831,90832,90833,91011,91299,93202,93352,93353,93965,95765,101261,118371,118372,157338,196657,199351,211068,213239,213243,213245,213247,213249,213251,213253,213255,213257,213259,213261,213263,213303,213305,213307,213309,213311,213313,213315,213317,213319,213321,213323,213325,213327,213329,213331,213333,213335,213339,213341,213343,213345,213347,213349,213351,213353,213355,213357,213359,213361,213363,213365) ) )
AND ( pn_postmeta.post_id IS NULL )
AND ((pn_posts.post_type = 'post'
AND (pn_posts.post_status = 'publish'
OR pn_posts.post_status = 'private')))
GROUP BY pn_posts.ID
ORDER BY pn_posts.post_date DESC
LIMIT 0, 1

    BTW, I’m using “pn” as my prefix on these tables.

    Here’s the table structure of both tables:

    pn_term_relationships:
    object_id, term_taxonomy_id, term_order

    pn_term_taxonomy:
    term_taxonomy_id, term_id, taxonomy, description, parent, count

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter jaypabs

    (@jaypabs)

    Update:

    I tried this but it seems that my site has slowed down after this ALTER command:

    ALTER TABLE pn_term_relationships
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (term_taxonomy_id, object_id),
  DROP KEY term_taxonomy_id,
  ADD KEY term_taxonomy_id (term_taxonomy_id, object_id, term_order),
  ADD KEY term_order (term_order, term_taxonomy_id, object_id);

    Is there something wrong with my ALTER command?

    • This reply was modified 2 years, 3 months ago by jaypabs.
    Plugin Author OllieJones

    (@olliejones)

    Thanks for letting me know what you have tried. I’ve been struggling, for about a year, to improve queries on the taxonomy tables. My efforts have not been rewarded with success, yet. I’m not giving up.

    There’s some work going on in the core of WordPress to cache the results of these queries. You may find it helpful to use a persistent object cache.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘How to Add Keys on wp_term_relationships and wp_term_taxonomy Table?’ is closed to new replies.