MySQL slow queries that could benefit from an index

I am helping someone with website performance, and noticed a couple slow queries that I believe came from this plugin (version 6.7.9). The first slow query is ran on the ahm_user_download_counts table and doesn’t use the primary key or another index.

# Query_time: 0.056898  Lock_time: 0.000002 Rows_sent: 1  Rows_examined: 192218
select count(ID) from wp_ahm_user_download_counts where user = '<ip_omitted>' and package_id = '647';

# Query_time: 0.224904  Lock_time: 0.000001 Rows_sent: 0  Rows_examined: 192218
UPDATE wp_ahm_user_download_counts SET download_count = '4' WHERE user = '<ip_omitted>' AND package_id = '647';

Adding this index reduces the rows_examined to 1.

mysql> alter table wp_ahm_user_download_counts add index package_id_user (package_id, user);
Query OK, 0 rows affected (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

The other queries relate to the ahm_sessions table:

# Query_time: 0.212994  Lock_time: 0.000001 Rows_sent: 0  Rows_examined: 172761
select value from wp_ahm_sessions where deviceID = '<omitted>' and name = 'uniq_<omitted>' and expire > 1755525194;

# Query_time: 0.201077  Lock_time: 0.000001 Rows_sent: 0  Rows_examined: 172761
DELETE FROM wp_ahm_sessions WHERE deviceID = '<omitted>' AND name = 'uniq_<omitted>';

# Query_time: 0.180426  Lock_time: 0.000001 Rows_sent: 0  Rows_examined: 172762
DELETE FROM wp_ahm_sessions WHERE name = 'download.114<ip_omitted>';

An index like this seems sufficient, at least on this site there don’t appear to be many deviceID’s per name.

mysql> alter table wp_ahm_sessions add index name_expire (name, expire);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

With this index, the rows_examined also drops to 1 for all 3 different queries.

Thanks for your attention on this, as I hope these small improvements can help all of your users. If you have further questions please let me know.

• This topic was modified 8 months, 2 weeks ago by justink.