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`)
)

Info:

`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


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


Sorry, only registered users may post in this forum.

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.