MySQL Forums
Forum List  »  Optimizer & Parser

optimizing searches using aggregate data in join
Posted by: Matthew Lenz
Date: October 21, 2019 09:05AM

pseudo schema:

table `widget` (
`id` int unsigned auto_increment primary key,
-- several dozen additional columns

table `widget_audit` (
`id` int unsigned auto_increment primary key,
`widget` int unsigned foreign key on `widget`,
`user` int unsigned foreign key on `user`,
`action` enum('event1','event2',...,'event30'),
`created` datetime default now(),
`details` text,
key `widget_action_created` (`widget`,`action`,`created`)


`widget` has 10s of millions of rows.
`widget_audit` has 10s-100s of millions of rows.
`widget_audit`.`action`s are not unique and can happen multiple times.

I have a situations where I need to efficiently work with (search AND sort (this is the real killer) ASC & DESC) MAX() `created` (DATETIMEs and DATE()s).

1. search `widget`s with `widget_audit`.`action` = 'eventX' AND MAX(`created`) between a DATETIME range.
2. access the other fields on the MAX(`created`) row.
2. sort `widget`s based on DATE(MAX(`widget_action`.`created`)) where `widget_audit`.`action` = 'eventX'

So none of this is super difficult and it's fast when we're not talking about 10's to 100's of millions of `widget_audit` records but unfortunately we are.

Of the 30ish actions there are only about a dozen that I need to keep track of the aggregate MAX() so I thought maybe I would add a trigger on `widget_audit` that does a replace into `widget_audit_agg` so there is always only a single row for any specific `action`. Or maybe putting individual columns in `widget` like `eventXcreated`

My concerns are the obvious duplication of data and needing to add even more indexes. I guess with either option i could probably drop the `widget_action_created` on `widget_audit` since it would no longer be used as much.

I've played around with joining on derived tables and while they aren't terribly slow they still aren't fast enough for interactive use (web application).

How would you guys attempt this?

Options: ReplyQuote

Written By
optimizing searches using aggregate data in join
October 21, 2019 09:05AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.