3M rows?

Any productive thoughts are appreciated. From Rackspace....

RACKER
Jun 13, 2015 - 8:26 PM PDT

Hello,

This Ticket has been created to inform you that your database has been disabled as your site www.xyz.com has been sending resource intensive queries to the database server once an hour which have degraded performance on the database server to the point that it is unusable for our other customers.

This is the query that your site has been issuing.

SELECT FROM_UNIXTIME(s.timestamp, '%Y%m%d') AS date, s.nid, COUNT(*) AS all_count, COUNT(DISTINCT s.ip_address) AS unique_count FROM simpleads_impressions s WHERE FROM_UNIXTIME(s.timestamp, '%Y%m%d') != FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y%m%d') GROUP BY date, s.nid

id: 1
select_type: SIMPLE
table: s
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3233268
Extra: Using where; Using filesort
1 row in set (0.25 sec)

This query is causing the database server to create a large temporary table of 3M rows and then perform a full sort on those rows. This is expending the memory and cpu of the server causing it to swap.

Please check into making these queries more efficient such as with reducing the number of records or adding an index, or removing these queries altogether from the site.

Please let us know when you have made changes to reduce the impact of these queries on the database server and if you have any questions, please contact us by phone, chat, or by responding to this ticket.

Jesse Cardenas
Systems Operations
Rackspace Cloud Sites
US: 1-877-934-0407
INTL +1.210.581.0407