I have a table (
notifications) containing about 300k entries. Each entry relates to a price change on one of about 500 different products (
I need to frequently pull the most recent
notifications entry for each
Trigger_ASIN. (I'll be using the resulting query to look up related details in another table)
My query as it currently stands is:
SELECT n1.Trigger_ASIN, n1.UniqueId from notifications n1 left outer join notifications n2 on n1.Trigger_ASIN = n2.Trigger_ASIN and n1.Trigger_TimeOfOfferChange < n2.Trigger_TimeOfOfferChange WHERE n2.UniqueId is NULL limit 20;
When limited to 20 records, it runs in 5secs (0 secs to fetch).
Limited to 40 records it takes 15 secs. Limited to 80 records it take 125 secs. By the time I get to my c.500 different products it's so slow it's unusable.
EXPLAIN output on the query shows that an index is being used (one one side of the join at least).
1 SIMPLE n1 index notifications__index_most_recent2 221 302974 Using index 1 SIMPLE n2 ref ix_notifications_Trigger_ASIN,notifications_Trigger_TimeOfOfferChange_index,notifications__index_most_recent_entry,notifications__index_most_recent2 notifications__index_most_recent_entry 63 amz.n1.Trigger_ASIN 240 Using where; Not exists; Using index
I've looked through a number of related questions, but still haven't managed to find the reason for the query running so slowly.
Can anyone see what I'm doing wrong to cause this to run so slowly?
For reference, the
notifications table and associated indices is are described below.
-- auto-generated definition CREATE TABLE notifications ( UniqueId VARCHAR(50) NOT NULL PRIMARY KEY, PublishTime DATETIME NULL, SellerId VARCHAR(30) NULL, MarketplaceId VARCHAR(20) NULL, Trigger_MarketplaceId VARCHAR(20) NULL, Trigger_ASIN VARCHAR(20) NULL, Trigger_ItemCondition VARCHAR(50) NULL, Trigger_TimeOfOfferChange DATETIME NULL ) ENGINE = InnoDB CHARSET = utf8; CREATE INDEX ix_notifications_PublishTime ON notifications (PublishTime); CREATE INDEX ix_notifications_SellerId ON notifications (SellerId); CREATE INDEX notifications__index_most_recent_entry ON notifications (Trigger_ASIN, Trigger_TimeOfOfferChange, UniqueId, Trigger_ItemCondition); CREATE INDEX ix_notifications_Trigger_ASIN ON notifications (Trigger_ASIN); CREATE INDEX ix_notifications_Trigger_ItemCondition ON notifications (Trigger_ItemCondition); CREATE INDEX notifications__index_most_recent2 ON notifications (Trigger_TimeOfOfferChange, Trigger_ASIN, UniqueId); CREATE INDEX notifications_Trigger_TimeOfOfferChange_index ON notifications (Trigger_TimeOfOfferChange);