how to speed up the mysql query to count average value?

Question

the table structure is follow:

CREATE TABLE `crm_member` (
 `member_id` int(11) NOT NULL AUTO_INCREMENT,
 `shop_id` int(11) NOT NULL,
 `nick` varchar(255) NOT NULL DEFAULT '',
 `name` varchar(255) NOT NULL DEFAULT '',
 `mobile` varchar(255) NOT NULL DEFAULT '',
 `grade` int(11) NOT NULL DEFAULT '-1',
 `trade_count` int(11) NOT NULL,
 `trade_amount` float NOT NULL,
 `last_trade_time` int(11) NOT NULL,
 `trade_from` tinyint(4) NOT NULL,
 `avg_price` float NOT NULL,
 `seller_flag` tinyint(1) NOT NULL,
 `is_black` tinyint(1) NOT NULL DEFAULT '0',
 `created` int(11) NOT NULL,
 PRIMARY KEY (`member_id`),
 UNIQUE KEY `shop_id` (`shop_id`,`nick`),
 KEY `last_trade_time` (`last_trade_time`),
 KEY `idx_shop_id_grade` (`shop_id`,`grade`),
 KEY `idx_shopid_created` (`shop_id`,`created`),
 KEY `idx_trade_amount` (`shop_id`,`trade_amount`),
 KEY `idx_trade_count` (`shop_id`,`trade_count`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

the table has 2148037 rows under the shop_id 3498706;

AND my query is like

SELECT AVG(trade_count) as trade_count, AVG(trade_amount) as trade_amount, AVG(grade) as grade from `crm_member_0141` WHERE shop_id = '3498706' and grade >= 0 and trade_count > 0 and is_black = 0 LIMIT 1

the query exec about 30 seconds .

the explain result

mysql> explain SELECT member_id, AVG(trade_count) as trade_count, AVG(trade_amount) as trade_amount, AVG(grade) as grade from `crm_member_0141` WHERE shop_id = 3498706 and grade >= 0 and trade_count > 0 and is_black = 0 order by member_id LIMIT 1;
+----+-------------+-----------------+------------+------+-------------------------------------------------------------------------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys                                                                 | key     | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-----------------+------------+------+-------------------------------------------------------------------------------+---------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | crm_member_0141 | NULL       | ref  | shop_id,idx_shop_id_grade,idx_shopid_created,idx_trade_amount,idx_trade_count | shop_id | 4       | const | 1074018 |     1.11 | Using where |
+----+-------------+-----------------+------------+------+-------------------------------------------------------------------------------+---------+---------+-------+---------+----------+-------------+

how to speed up the query?

Have you consider to store the result? I mean if it's a frequent query it may be the best option. You can also index the fields to speed it up. — Elanochecer, Mar 27 '19 at 09:11
If you can you should split your query in 3 queries to avoid condition != 0: in the first query use sum, then 2 queries : select count where grade >= 0 & select count where trade_count > 0 and do the calculation yourself — Ianis, Mar 27 '19 at 10:49

LeGEC · Answer 1 · 2019-03-27T09:58:09.920

It looks like mysql chooses to use the primary key instead of the shop_id, grade index, although shop_id, grade would probably speed up the selection of rows.

You can tell mysql to use a specific index for a query, using the USE INDEX directive :

try to add USE INDEX (idx_shop_id_grade) in your query after the table name and see if the computation is faster.

Otherwise, if this query is especially useful and frequent to call in your app, you can build a more specialised index for this query :

Your query selects on a particular value for shop_id and is_black, then does a range selection on grade and trade_count.

I would suggest to try indexing using shop_id, is_black, grade, trade_count.

note : obviously, test this first on test database

how to speed up the mysql query to count average value?

1 Answers1