Monday, 9 September 2013

How to optimize the performance of a MySQL view

How to optimize the performance of a MySQL view

I have a view defined as follows:
CREATE VIEW vw_data_summary AS
SELECT
a.b_id AS b_id
b.txt_field AS b_txt_field
a.txt_field AS a_txt_field
SUM(a.amount) AS amount
FROM
a JOIN b
WHERE
a.b_id = b.id
GROUP BY
a.b_id, b.txt_field, a.txt_field
Table a has 650,000 records, while table b has 3,200 records. There is an
index on a.b_id. The tables grow at a rate of 1,500 records per day on
table a, and 50 records per day on table b.
When I run a select on the view, it takes about 25 seconds. I'm concerned
that as the tables continue to grow in size, the performance of this query
is going to rapidly degrade.
What can I do, to either the individual tables, or to the view itself, to
improve the performance?
EXPLAIN
Running explain on the select above yields the following output:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ALL PRIMARY NULL NULL NULL 3078 Using
temporary; Using filesort
1 SIMPLE a ref b_id b_id 4 b.id 134

No comments:

Post a Comment