4

We use the community software Invision Power Suite 4.1.15. The community has 15+ million posts and we've set up the search index table it has to contain a year of posts.

We discovered the following query in our slow log:

# Query_time: 6466.229122  Lock_time: 0.000053 Rows_sent: 0 Rows_examined: 2287405946
SET timestamp=1475416358;
/*IPS\Content\Search\Mysql\_Index::prune:179*/
    DELETE FROM `ibf_core_search_index_item_map`
        WHERE index_item_id IN(
            SELECT index_item_id
                FROM `ibf_core_search_index` AS `core_search_index`
                WHERE index_date_updated < 1443873892 );

As you can see this query apparently examines 2.2 billion rows. Which is... bad...

However, a count on the table core_search_index_item_map table gives only 434926 rows. And a count on ibf_core_search_index gives 1559806 rows.

Furthermore, if I run the following query now (the subselect-query from above)

SELECT index_item_id FROM `ibf_core_search_index` AS `core_search_index`
    WHERE index_date_updated < 1443873892

I'm being returned 99 rows.

And if I run the following select query (I've replaced DELETE FROM with SELECT * FROM):

SELECT * FROM `ibf_core_search_index_item_map`
    WHERE index_item_id IN(
        SELECT index_item_id
            FROM `ibf_core_search_index` AS `core_search_index`
                WHERE index_date_updated < 1443873892 );

Then I'm being returned 301 rows.

The explain of the select query gives this: http://pastebin.com/kivhZ0An

While an explain of the delete query gives this: http://pastebin.com/0Pzvi5Qg

Even though all of the select queries separately gives way less rows than 2.28 billion rows, that's apparently what the delete query is working with. And this have happened consistently for the past few days when that delete query has been initiated, so it's not just a one-off.

This caused serious issues in our community at the time it was running. And the software IPS automatically wants to run this query once a day. For now I've disabled it, but I need to understand how this is happening before I want to report it to them.

Here is the show create statement for both tables involved: CREATE TABLES

MySQL [*]> SHOW CREATE TABLE ibf_core_search_index_item_map\G
*************************** 1. row ***************************
       Table: ibf_core_search_index_item_map
Create Table: CREATE TABLE "ibf_core_search_index_item_map" (
  "index_author_id" int(10) NOT NULL DEFAULT '0',
  "index_item_id" int(10) NOT NULL DEFAULT '0',
  "index_class" varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  UNIQUE KEY "search_map" ("index_author_id","index_item_id","index_class")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

MySQL [*]> SHOW CREATE TABLE ibf_core_search_index\G
*************************** 1. row ***************************
       Table: ibf_core_search_index
Create Table: CREATE TABLE "ibf_core_search_index" (
  "index_class" varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Content type class e.g. \\IPS\\forums\\Topic',
  "index_id" bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  "index_object_id" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The ID of the object. pid for posts, tid for topics etc',
  "index_item_id" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'For comments and reviews, the ID of the item it pertains to. For \\IPS\\Content\\Item this would just be NULL',
  "index_container_id" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The id of the container for items/comments/reviews',
  "index_content" mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The plain-text content to search',
  "index_permissions" text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'A comma-delimited list of groups which have permission to view',
  "index_author" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The object author id',
  "index_title" varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Content title',
  "index_date_created" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The object creation date',
  "index_date_updated" int(10) unsigned DEFAULT NULL COMMENT 'Object last updated date',
  "index_hidden" tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 = visible, -1 = hidden (previously visible), 1 = unapproved, 2 = parent item hidden',
  "index_item_index_id" bigint(20) unsigned DEFAULT NULL COMMENT 'The index ID of the item',
  "index_item_author" mediumint(8) DEFAULT NULL COMMENT 'The author of the item',
  "index_is_last_comment" tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is this the last comment/review on an item?',
  PRIMARY KEY ("index_id"),
  UNIQUE KEY "object" ("index_class"(181),"index_object_id"),
  KEY "author_lookup" ("index_author","index_hidden","index_date_updated"),
  KEY "item" ("index_class"(181),"index_item_id"),
  KEY "container" ("index_class"(181),"index_container_id"),
  KEY "index_date_updated" ("index_date_updated"),
  KEY "index_date_created" ("index_date_created"),
  KEY "index_hidden" ("index_hidden"),
  FULLTEXT KEY "index_content" ("index_content","index_title"),
  FULLTEXT KEY "index_title" ("index_title")
) ENGINE=InnoDB AUTO_INCREMENT=1571104 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

We use Mysql 5.7.15-0ubuntu0.16.04.1-log

4

MySQL's optimizer never had many options when optimizing IN with subqueries:

WHERE column IN (SELECT ....)

In 5.6 and 5.7 versions there were several improvements in that area but seems like there are still cases when it fails to find the best plan.

I suggest you rewrite the query using JOIN:

DELETE item_map.* 
FROM ibf_core_search_index_item_map AS item_map
  JOIN ibf_core_search_index AS core_search_index
    ON item_map.index_item_id = core_search_index.index_item_id 
WHERE core_search_index.index_date_updated < 1443873892 ;

or (to lead the optimizer to materialize the sub result):

DELETE item_map.* 
FROM ibf_core_search_index_item_map AS item_map
  JOIN 
    ( SELECT index_item_id 
      FROM ibf_core_search_index 
      WHERE index_date_updated < 1443873892 
    ) AS core_search_index
  ON item_map.index_item_id = core_search_index.index_item_id ;

Regarding why your DELETE and SELECT are showing different plans: Not sure about all the optimizer internals but sometimes these additional options/improvements are not added for all statements at the same time. I.e. an improvement may be made that affects only SELECT statements and not UPDATE/DELETE ones, at version X and then in a later version Y, these are added. And the details may differ (due to locking considerations, isolation levels etc.) Modifying the table's data is different than just selecting them.

As for the 2.2 billion, that's about (1.5M / 300) * 434K.


Other things that affect efficiency:

  • The columns (index_item_id) have different types in the 2 tables. One is signed int, the other unsigned int. Change one of them so they match.
  • There is no index on the item_map (index_item_id).
  • An index on (index_date_updated, index_item_id) would help, too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.