[Bug]: Post_Link_Indexing_Action->get_count_query() extremely slow

[adconecto]

Prerequisites

• I've read and understood the contribution guidelines.
• I've searched for any related issues and avoided creating a duplicate issue.

Please give us a description of what happened

On a WordPress installation with:

• ~25,000 rows in wp_posts
• ~33,000 rows in wp_yoast_seo_links

the query in Post_Link_Indexing_Action->get_count_query() takes more than 60 seconds to complete.

Given the moderate dataset size, this appears to be caused by the query structure rather than dataset scale.

The problematic pattern is:

• LEFT JOIN on wp_yoast_indexable
• LEFT JOIN on wp_yoast_seo_links
• WHERE ( I.object_id IS NULL OR L.post_id IS NOT NULL )

This LEFT JOIN + OR combination prevents efficient index usage and leads to inefficient execution plans.

Step-by-step reproduction instructions

1. Use a WordPress installation with > 20k posts
2. Ensure Yoast SEO indexables and internal links are generated
3. Trigger internal link indexing
4. Observe execution time of Post_Link_Indexing_Action->get_count_query()
5. Query execution takes a very long time (> 60s on our installation)

Expected results

Query should execute in milliseconds to low seconds.

Actual results

• Execution time > 60 seconds
• High DB load

Screenshots, screen recording, code snippet

Rewrite using EXISTS / NOT EXISTS instead of LEFT JOIN + OR, for example:

SELECT COUNT(*)
FROM wp_posts AS P
WHERE P.post_status = 'publish'
  AND P.post_type IN (...)
  AND (
    NOT EXISTS (
        SELECT 1
        FROM wp_yoast_indexable AS I
        WHERE I.object_id = P.ID
          AND I.object_type = 'post'
          AND I.link_count IS NOT NULL
    )
    OR EXISTS (
        SELECT 1
        FROM wp_yoast_seo_links AS L
        WHERE L.post_id = P.ID
          AND L.type = 'internal'
          AND L.target_indexable_id IS NULL
          AND L.target_post_id IS NOT NULL
          AND L.target_post_id != 0
    )
  );

Updated queries in class Post_Link_Indexing_Action:

protected function get_count_query() {
    $public_post_types = $this->post_type_helper->get_indexable_post_types();
    $indexable_table   = Model::get_table_name( 'Indexable' );
    $links_table       = Model::get_table_name( 'SEO_Links' );

    // Warning: If this query is changed, makes sure to update the query in get_select_query as well.
    return $this->wpdb->prepare(
        "SELECT COUNT(*)
         FROM {$this->wpdb->posts} AS P
         WHERE P.post_status = 'publish'
           AND P.post_type IN (" . \implode( ', ', \array_fill( 0, \count( $public_post_types ), '%s' ) ) . ")
           AND (
             NOT EXISTS (
                 SELECT 1
                 FROM $indexable_table AS I
                 WHERE I.object_id = P.ID
                   AND I.object_type = 'post'
                   AND I.link_count IS NOT NULL
             )
             OR EXISTS (
                 SELECT 1
                 FROM $links_table AS L
                 WHERE L.post_id = P.ID
                   AND L.type = 'internal'
                   AND L.target_indexable_id IS NULL
                   AND L.target_post_id IS NOT NULL
                   AND L.target_post_id != 0
             )
           )",
        $public_post_types
    );
}

protected function get_select_query( $limit = false ) {
    $public_post_types = $this->post_type_helper->get_indexable_post_types();
    $indexable_table   = Model::get_table_name( 'Indexable' );
    $links_table       = Model::get_table_name( 'SEO_Links' );
    $replacements      = $public_post_types;

    $limit_query = '';
    if ( $limit !== false ) {
        $limit_query    = 'LIMIT %d';
        $replacements[] = (int) $limit;
    }

    // Warning: If this query is changed, makes sure to update the query in get_count_query as well.
    return $this->wpdb->prepare(
        "SELECT P.ID, P.post_content
        FROM {$this->wpdb->posts} AS P
        WHERE P.post_status = 'publish'
          AND P.post_type IN (" . \implode( ', ', \array_fill( 0, \count( $public_post_types ), '%s' ) ) . ")
          AND (
            NOT EXISTS (
                SELECT 1
                FROM $indexable_table AS I
                WHERE I.object_id = P.ID
                  AND I.link_count IS NOT NULL
                  AND I.object_type = 'post'
            )
            OR EXISTS (
                SELECT 1
                FROM $links_table AS L
                WHERE L.post_id = P.ID
                  AND L.target_indexable_id IS NULL
                  AND L.type = 'internal'
                  AND L.target_post_id IS NOT NULL
                  AND L.target_post_id != 0
            )
          )
        $limit_query",
        $replacements
    );
}

Please implement those changes - query time is reduced from > 60 seconds to ~0.2 seconds.

Which editor is affected (or editors)

• Block Editor
• Gutenberg Editor
• Elementor Editor
• Classic Editor
• Other (please specify in additional info)

Which browser is affected (or browsers)

• Chrome
• Firefox
• Safari
• Other (please specify in additional info)

Device you are using

No response

Operating system

No response

PHP version

8.2

WordPress version

6.8.3

WordPress Theme

No response

Yoast SEO version

26.9

Gutenberg plugin version (if relevant)

No response

Elementor plugin version (if relevant)

No response

Classic Editor plugin version (if relevant)

No response

Relevant plugins in case of a bug

No response

[josevarghese]

Hi @adconecto

Thanks for reporting and for your suggestions. To properly evaluate the proposed query changes, our development team requires additional technical details. As performance improvements must be assessed on a case-by-case basis, we kindly ask you to provide the following:

1. A detailed explanation of both the original query and the rewritten query, including their respective outputs. This will help us understand the execution plan and determine whether the proposed changes result in measurable performance improvements to the indexable.
2. Also, share some supporting evidence for the performance comparison. You mentioned that execution time was previously around 60 seconds or more and has now improved by a few seconds. Please share benchmark results or logs that demonstrate this improvement.

Without a clear explanation, analysis, and objective performance data, we are unable to assess whether the suggested solution offers a meaningful benefit.

We appreciate your cooperation and look over the additional details to take a closer look on it.

[adconecto]

Hi @josevarghese, I created a pull request: #23000

Original query (count):

SELECT COUNT(P.ID)
FROM wp_posts AS P
LEFT JOIN wp_yoast_indexable AS I
	ON P.ID = I.object_id
	AND I.link_count IS NOT NULL
	AND I.object_type = 'post'
LEFT JOIN wp_yoast_seo_links AS L
	ON L.post_id = P.ID
	AND L.target_indexable_id IS NULL
	AND L.type = 'internal'
	AND L.target_post_id IS NOT NULL
	AND L.target_post_id != 0
WHERE ( I.object_id IS NULL OR L.post_id IS NOT NULL )
	AND P.post_status = 'publish'
	AND P.post_type IN ('post', 'page', 'event', 'ort', 'liste', 'links', 'livecams', 'gewinnspiel', 'voting', 'entity', 'web-story');

Optimized query:

SELECT COUNT(*)
FROM wp_posts AS P
WHERE P.post_status = 'publish'
  AND P.post_type IN ('post', 'page', 'event', 'ort', 'liste', 'links', 'livecams', 'gewinnspiel', 'voting', 'entity', 'web-story')
  AND (
    NOT EXISTS (
        SELECT 1
        FROM wp_yoast_indexable AS I
        WHERE I.object_id = P.ID
          AND I.object_type = 'post'
          AND I.link_count IS NOT NULL
    )
    OR EXISTS (
        SELECT 1
        FROM wp_yoast_seo_links AS L
        WHERE L.post_id = P.ID
          AND L.type = 'internal'
          AND L.target_indexable_id IS NULL
          AND L.target_post_id IS NOT NULL
          AND L.target_post_id != 0
    )
  )

Original query (get):

SELECT P.ID, P.post_content
FROM wp_posts AS P
LEFT JOIN wp_yoast_indexable AS I
	ON P.ID = I.object_id
	AND I.link_count IS NOT NULL
	AND I.object_type = 'post'
LEFT JOIN wp_yoast_seo_links AS L
	ON L.post_id = P.ID
	AND L.target_indexable_id IS NULL
	AND L.type = 'internal'
	AND L.target_post_id IS NOT NULL
	AND L.target_post_id != 0
WHERE ( I.object_id IS NULL OR L.post_id IS NOT NULL )
	AND P.post_status = 'publish'
	AND P.post_type IN ('post', 'page', 'event', 'ort', 'liste', 'links', 'livecams', 'gewinnspiel', 'voting', 'entity', 'web-story');

Optimized query (get):

SELECT P.ID, P.post_content
FROM wp_posts AS P
WHERE P.post_status = 'publish'
  AND P.post_type IN ('post', 'page', 'event', 'ort', 'liste', 'links', 'livecams', 'gewinnspiel', 'voting', 'entity', 'web-story')
  AND (
    NOT EXISTS (
        SELECT 1
        FROM wp_yoast_indexable AS I
        WHERE I.object_id = P.ID
          AND I.link_count IS NOT NULL
          AND I.object_type = 'post'
    )
    OR EXISTS (
        SELECT 1
        FROM wp_yoast_seo_links AS L
        WHERE L.post_id = P.ID
          AND L.target_indexable_id IS NULL
          AND L.type = 'internal'
          AND L.target_post_id IS NOT NULL
          AND L.target_post_id != 0
    )
  )

You can compare the results on your wordpress database by just executing them.

Query time of the original count query - 59 seconds:

Query time of the optimized count query - 0.15 seconds:

Query time of the original get query - 1 minute and 2 seconds:

Query time of the optimized get query - 0.17 seconds:

My wp_posts table has 27000 rows, wp_yoast_indexable 11000 rows and wp_yoast_seo_links 34000 rows.

[amboutwe]

Please inform the customer of conversation # 1364544 when this conversation has been closed.

[amboutwe]

Please inform the customer of conversation # 1363681 when this conversation has been closed.