Drop An Index To Speed Up AdRotate WordPress Plugin

We have a client that keeps encountering a tremendous load on their WordPress Mysql database despite a trivial amount of normal traffic on their blog and lots of tuning that eliminated most normal database access.

We tracked this down to the AdRotate plugin, and specifically the tracking callbacks to the admin-ajax interface in WordPress.

Each one of these callbacks runs a select query against the wp_adrotate_tracker table, and each was taking ~1.3 seconds to run.

The table had nearly 2 million rows. Digging into the source code, the table is supposed to be cleaned up regularly by wp_cron, but the author misspelled database column names, rendering the cleanup queries useless.  We reached out to the author and truncated the table and hoped it would take a long time to accumulate so many rows and that perhaps the author would respond by then.

Unfortunately, the author has never responded to our bug report and the site again reached a crawl. We decided to take a different tactic.

The table in question:

CREATE TABLE `wp_adrotate_tracker` (
  `id` bigint(9) unsigned NOT NULL AUTO_INCREMENT,
  `ipaddress` varchar(255) NOT NULL DEFAULT '0',
  `timer` int(15) NOT NULL DEFAULT '0',
  `bannerid` int(15) NOT NULL DEFAULT '0',
  `stat` char(1) NOT NULL DEFAULT 'c',
  `useragent` mediumtext NOT NULL,
  `country` text NOT NULL,
  `city` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ipaddress` (`ipaddress`),
  KEY `bannerid` (`bannerid`),
  KEY `timer` (`timer`)
) ENGINE=InnoDB AUTO_INCREMENT=1818926 DEFAULT CHARSET=utf8

The relevant explain plan:

+----+-------------+---------------------+-------------+--------------------+--------------------+---------+------+------+------------------------------------------------------------------+

| id | select_type | table               | type        | possible_keys      | key                | key_len | ref  | rows | Extra                                                            |

+----+-------------+---------------------+-------------+--------------------+--------------------+---------+------+------+------------------------------------------------------------------+

|  1 | SIMPLE      | wp_adrotate_tracker | index_merge | ipaddress,bannerid | ipaddress,bannerid | 767,4   | NULL | 2113 | Using intersect(ipaddress,bannerid); Using where; Using filesort |

+----+-------------+---------------------+-------------+--------------------+--------------------+---------+------+------+------------------------------------------------------------------+

This is a red flag to us. Why do we need an index_merge between ipaddress and bannerid? Surely ipaddress is highly selective and bannerid … isn’t. This could mean really poor performance. To confirm our suspicions, we checked the selectivity of bannerid:

+----------+
| bannerid |
+----------+
|        3 |
|        4 |
|        5 |
+----------+

Ouch. That means the database may be doing an index_merge of a third of 2 million rows against a handful of rows actually returned by the ipaddress index. If this is true, it means the database is doing an absurd amount of extra work sorting hundreds of thousands of rows – probably on disk – to no benefit. To confirm our theory, we dropped the index:

alter table wp_adrotate_tracker drop key bannerid;

Our new explain:

+----+-------------+---------------------+------+---------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table               | type | possible_keys | key       | key_len | ref   | rows | Extra                       |
+----+-------------+---------------------+------+---------------+-----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | wp_adrotate_tracker | ref  | ipaddress     | ipaddress | 767     | const |    1 | Using where; Using filesort |
+----+-------------+---------------------+------+---------------+-----------+---------+-------+------+-----------------------------+

No more index_merge!

How does it actually run? These queries dropped from ~1.3 seconds to ~0.1 seconds. The load on the database dropped from 13 to 0.3. Note that, had bannerid been highly selective, this wouldn’t necessarily be a win. However, the likelihood of having so many banners in this system that this is beneficial seems low.

We’ve seen over-indexing many times by developers and even highly paid DBAs who don’t understand the full impact of indexes. Simply adding indexes on every column ever used in a where clause is a naive indexing strategy that can seriously impact your performance.

Are you sure you understand the impact of your indexes?