1

I have a table (notifications) containing about 300k entries. Each entry relates to a price change on one of about 500 different products (Trigger_ASIN).

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.

The 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.

Notifications Table:

    -- 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);
2
  • Are you sure you need VARCHAR(20) for triggers? String values search is very expensive. Each time charset/collation conversation applied as well as array comparison instead of single integer one. If you need some descriptive values create another table Trigger_Value store ID -- Description pairs there and use integer Trigger_Value.ID instead of VARCHAR().
    – Kondybas
    May 29, 2018 at 17:29
  • @Kondybas - thanks for your comment. The Triggger_Value is an alphanumeric string (e.g. B00CU65FOI) but should never be more than 10 chars long. Is there likely to be a performance improvement by reducing the allocation from 20 to 10? I like your idea of keeping a separate table of trigger_value IDs - I'll give this a go and see how it effects performance.
    – pa1983
    May 30, 2018 at 8:26

1 Answer 1

1
SELECT n1.Trigger_ASIN
     , n1.UniqueId
FROM notifications n1
JOIN ( SELECT n2.Trigger_ASIN
            , MAX(n2.Trigger_TimeOfOfferChange) Trigger_TimeOfOfferChange 
       FROM notifications n2 
       GROUP BY n2.Trigger_ASIN ) n3
WHERE n1.Trigger_ASIN = n3.Trigger_ASIN
  AND n1.Trigger_TimeOfOfferChange = n3.Trigger_TimeOfOfferChange
/* ORDER BY something -- obligatorily, limitation does not make sense without it!!! */ 
LIMIT 20;

And ensure the notifications__index_most_recent_entry index is used.

1
  • Many thanks @Akina - that did the trick. The query now runs in 0.125s with no limit set (I only had the limit 20 in place to speed it up while testing). Out of interest, is there any difference in this case in writing the comparison in a WHERE rather than as a JOIN ON?
    – pa1983
    May 30, 2018 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.