CODEWITHSUNDEEP
mysql
Jack he
Jack he

Reputation: 27

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

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?

Upvotes: 0

Views: 435

Answers (1)

LeGEC
LeGEC

Reputation: 52081

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

Upvotes: 1

Related Questions