search

box / Anemometer

Box SQL Slow Query Monitor
Apache License 2.0
1.39k stars 316 forks source link

Default page loads slowly when _review_history table contains lots of records ... #70

Closed greenlitdesign closed 10 years ago

greenlitdesign commented 11 years ago

The index on history table is not appropriate as your query on the main page is doing a date range search. Currently, Mysql does a full scan (index scan) because it has to get all ts_min

I added an index on history table and reponse is immediate. Create index idx_ts on global_query_review_history(ts_min)

SELECT checksum AS checksum, LEFT(dimension.sample,20) AS snippet, ROUND(SUM(Rows_examined_sum)/SUM(rows_sent_sum),2) AS index_ratio, SUM(Query_time_sum) / SUM(ts_cnt) AS query_time_avg, ROUND(SUM(Rows_sent_sum)/SUM(ts_cnt),0) AS rows_sent_avg, SUM(ts_cnt) AS ts_cnt, SUM(Query_time_sum) AS Query_time_sum, SUM(Lock_time_sum) AS Lock_time_sum, SUM(Rows_sent_sum) AS Rows_sent_sum, SUM(Rows_examined_sum) AS Rows_examined_sum, SUM(Tmp_table_sum) AS Tmp_table_sum, SUM(Filesort_sum) AS Filesort_sum, SUM(Full_scan_sum) AS Full_scan_sum FROM global_query_review_psql0 AS fact JOIN global_query_review_history_psql0 AS dimension USING (checksum) WHERE dimension.ts_min >= "2013-01-17 13:43:08" AND dimension.ts_min <= "2013-01-18 13:43:08" GROUP BY checksum

geoffreyanderson commented 11 years ago

As far as I can tell that key has always existed in the install.sql file we have (see: https://github.com/box/Anemometer/blob/master/install.sql#L122). Is there another index you added besides just "ts_min"?

gtowey commented 10 years ago

This is correct, the index is in the install.sql file and should be created by default. It's possible you installed this quite a while ago? If so thanks for being an early adopter! =)