Re: speed up query

[Rick James]

If you can devise an adequate set of Summary tables, then do the following when a batch comes in (twice a day):

1. Batch load into a new MyISAM table, say `tmp` with no indexes. (For HA, etc, the data can be reloaded.) (You will be doing only table scans, so don't bother with indexes.)

2. Run scripts to build summary info:
INSERT INTO Summary1 SELECT ... FROM tmp GROUP BY ...
Note: You have the luxury of doing this step with very little impact on what anyone else is doing.

3. If you really need the raw data,
INSERT INTO dsl_data SELECT * FROM tmp

4. DROP TABLE tmp.

Once the summary tables are in place, and the reports are hitting the summary tables, analyze which index(es) on the main table(s) can be dropped. (This will improve INSERT/DELETE performance on them.)

If your queries tend to be _exactly_ repeated several times a day, set query_cache_type = DEMAND, use SQL_CACHE in the SELECTs that might be repeated, and set query_cache_size to, say 400M. This will make _some_ of your queries instantaneous.

Doing all of the above should lead do a relatively idle system, and the report-type queries should be much faster than they currently are.