Very long database queries

As a temporary solution, which I do not prefer as much, but it performs a similar task, is the plugin called Fabrica Reusable Block Instances, https://www.ads-software.com/plugins/fabrica-reusable-block-instances/.

The issue with that plugin is that it also does not optimize database queries and instead calls them on every row. However, its query should be slightly faster as it only counts elements, SELECT COUNT(*).

Until the author here optimizes this plugin and combines all queries into one, you can use that plugin alternative.

I have created a single SQL command that reads all “reusable” blocks from all articles in a single pass, and creates a list of all articles in which the block is located. It also correctly counts the number of times the block appears and the total number of pages on which the block appears.

Note that this query returns results in ALL articles, not just the first page of results, such as the first 20 or first 100.

CREATE TEMPORARY TABLE results (post_id INT UNSIGNED, block_id INT UNSIGNED) ENGINE=MEMORY CHARSET=utf8mb4;
DROP PROCEDURE IF EXISTS extractor;

DELIMITER //

CREATE PROCEDURE extractor() BEGIN

  DECLARE row_content, string, the_rest, pattern TEXT CHARSET utf8;
  DECLARE row_id, row_block INT UNSIGNED;

  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE result CURSOR FOR SELECT id, post_content FROM wp_posts WHERE post_content LIKE '%<!-- wp:block {%' AND post_type NOT IN ('revision', 'attachment', 'nav_menu_item');
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  SET pattern = '<!-- wp:block {"ref":';

  OPEN result;
  loop_posts: LOOP

    FETCH result INTO row_id, row_content;

    IF done THEN
      LEAVE loop_posts;
    END IF;

    SET string = row_content;

    BEGIN
    loop_string:LOOP

      IF CHAR_LENGTH(TRIM(string)) = 0 OR string IS NULL OR LOCATE(pattern, string) = 0 THEN
        LEAVE loop_string;
      END IF;

      SET string= SUBSTRING(string, LOCATE(pattern, string) + CHAR_LENGTH(pattern));
      SET @temp = REGEXP_SUBSTR(string, "^\\d+\\D");
      SET @temp = LEFT(@temp, CHAR_LENGTH(@temp) - 1);
      SET row_block = CAST(@temp AS UNSIGNED);

      INSERT INTO results VALUES (row_id, row_block);

    END LOOP loop_string;
    END;


  END LOOP loop_posts;

  CLOSE result;
END //

DELIMITER ;

CALL extractor();

# Just IDs, grouped and sorted
# SELECT block_id AS reusable_block_id, COUNT(post_id) AS number_of_block_occurrences, COUNT(DISTINCT post_id) AS number_of_pages_with_block, GROUP_CONCAT(post_id ORDER BY post_id) AS list_of_post_ids FROM results GROUP BY block_id;

# All the data that we need for display: Post ID for a link, Post Title and Post Type, grouped and sorted
SELECT block_id, CONCAT('[', GROUP_CONCAT(JSON_ARRAY(post_id, posts.post_title, posts.post_type) ORDER BY post_id), ']') FROM results LEFT JOIN wp_posts as posts ON results.post_id = posts.id GROUP BY block_id;

# We could also use JSON_OBJECTAGG if we have some of later MariaDB/MySQL
SELECT block_id, CONCAT('[', GROUP_CONCAT(JSON_ARRAY(post_id, posts.post_title, posts.post_type) ORDER BY post_id), ']') FROM results LEFT JOIN wp_posts as posts ON results.post_id = posts.id GROUP BY block_id;

Note that this query will locate all reusable blocks in the content, including ones that are no longer present in the current WordPress installation.

If you can, please test this SQL command in Adminer, for example, using the Adminer plugin. I am really interested in how much faster it is compared to the multitude of queries that WP currently sends. If it is significantly faster, it would make sense to incorporate this query into the plugin and I am sure the author of this plugin will do so. Or allow me to submit a PR for an improvement.