CODEWITHSUNDEEP
mysqlsqlperformanceoptimizationquery-performance
Pavel Straka
Pavel Straka

Reputation: 427

MySQL - how to speed up SELECT SUM

I have a MySQL table like this:

CREATE TABLE `goods_flow` (
 `cycle_id` int(11) NOT NULL,
 `subject_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
 `origin_id` int(11) NOT NULL,
 `flow_value` int(11) NOT NULL,
 PRIMARY KEY (`cycle_id`,`origin_id`,`subject_id`),
 KEY `fk_goods_flow_subjects` (`subject_id`),
 KEY `fk_goods_flow_origins` (`origin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and the application often uses this query:

SELECT SUM(flow_value) AS 'amount_of_goods' FROM goods_flow WHERE subject_id = 'xyz';

which takes a long time.

How could I speed up the query execution? Thank You

Upvotes: 2

Views: 2385

Answers (2)

Rick James
Rick James

Reputation: 142278

Is there some reason for the order of the 3 columns in the PRIMARY KEY? If not, then shuffle them so that subject_id is first. (And fiddle with the secondary keys.)

If it is taking 500ms to read 57 rows, then it sounds like a very cold cache. What happens if you run it twice in a row?

What is the value of innodb_buffer_pool_size? It is the relevant cache. And how much RAM do you have?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

This is your query:

SELECT SUM(flow_value) AS amount_of_goods
FROM goods_flow
WHERE subject_id = 'xyz';

You can speed this query by using a composite index. The best index is goods_flog(subject_id, flow_value). This index is a covering index for the query, meaning that all the data needed by the query is in the index, so the original data pages do not need to be accessed.

Upvotes: 3

Related Questions