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?
Aurelian Anghelescu
March 30, 2015 @ 5:54 pm
Hello there,
What you say seems right, but i have a problem when running the query
alter table wp_adrotate_tracker drop key bannerid;
I get;
MySQL said:
#1091 – Can’t DROP ‘bannerid’; check that column/key exists
What should i do here?
Thank you.
Internet Staff
March 30, 2015 @ 6:01 pm
I think you’ll want to run show create_table wp_adrotate_tracker; and see what’s actually there. 🙂
kāts
November 22, 2015 @ 7:48 am
Plugin author don’t know how to use them properly.
https://wordpress.org/support/topic/adrotate-query-issues-easily-solved-too?replies=21#post-2637273