CODEWITHSUNDEEP
mysqlperformanceinnodb
Elie
Elie

Reputation: 13855

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

TABLE DEFINITIONS

CREATE TABLE IF NOT EXISTS `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt_field` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `txt_field` (`txt_field`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `b_id` int(11) NOT NULL,
  `txt_field` varchar(255) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `txt_field` (`txt_field`),
  KEY `b_id` (`b_id`),
  KEY `new_index` (`txt_field`,`b_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Upvotes: 3

Views: 19170

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

Add an index on (b_id, txt_field, amount) and create the view with:

CREATE VIEW vw_data_summary_2 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
      ON
    a.b_id = b.id
GROUP BY
    a.b_id, a.txt_field ;

If you had a foreign key in a(b_id) that references b(id) you could also try changing the JOIN to LEFT JOIN and compare performance. In that case, the view could also be defined with:

CREATE VIEW vw_data_summary_4 AS
SELECT
    a.b_id AS b_id
    (SELECT b.txt_field FROM b WHERE a.b_id = b.id) AS b_txt_field
    a.txt_field AS a_txt_field
    SUM(a.amount) AS amount
FROM
    a 
GROUP BY
    a.b_id, a.txt_field ;

Upvotes: 4

Duniyadnd
Duniyadnd

Reputation: 4043

You're using InnoDB - glad you mentioned that.

Here's a checklist:

  • Optimize your table

This will reorganize your table on your server for quicker input/output

OPTIMIZE TABLE b;
OPTIMIZE TABLE a;

Check out more about InnoDB Optimization at: MySQL

What I don't understand is why you have created a key for each and every single column of yours, that is a little redundant - as you could just tie in multiple columns as a single key (composite index) - especially if you are only going to compare a single column to another column from another table. Ideally, you created an index in the same order as the group by as well.

Also logically, they would only be comparing a single key index when using the GROUP BY rather than sorting through each key and then placing them next to the correct value.

  • a.b_id - could be a FOREIGN KEY

  • Lastly - check your memory, consider using InnoDB's buffer options as well.

Hope that helps. To assist others, if one of these assisted you, leave a comment which one had the biggest impact.

Upvotes: 2

Related Questions