Re: Load Data Infile takes a long time
Posted by:
Rick James
Date: March 20, 2013 05:03PM
> Typical Select queries would be looking for date, project_id & metric_id.
SELECT ... WHERE project_id = ... AND metric_id = ... AND date BETWEEEN...
begs for the index that you have:
KEY `game_metric_date` (`project_id`,`metric_id`,`date`)
In particular, `date` should _not_ be first, since you are already partitioning on that.
Variants on that SELECT (different set of fields, IN, "<",etc) may need other indexes.
You could rearrange the PK to start with `project_id`,`metric_id`,`date`; that would further optimize _that_ SELECT.
> There would be aggregation done as well.
Build summary table(s), grouped by day and other field(s).