MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Load Data Infile takes a long time
1673
March 20, 2013 05:03PM


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.