The sum is slow-CodePudding

Ladies and gentlemen, I now have a database

 CREATE TABLE ` wallet_change ` (
` id ` bigint (20) NOT NULL AUTO_INCREMENT, 
` created_at ` datetime (0) NOT NULL DEFAULT CURRENT_TIMESTAMP, 
` update_at ` datetime (0) NOT NULL DEFAULT CURRENT_TIMESTAMP, 
` version ` bigint (20) NOT NULL DEFAULT 0, 
` changed_amount ` bigint (20) NULL DEFAULT 0, 
` description ` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
` last_amount ` bigint (20) NULL DEFAULT 0, 
` payment ` bigint (20) NULL DEFAULT 0, 
` type ` int (11) NULL DEFAULT NULL, 
` wallet ` bigint (20) NULL DEFAULT NULL, 
PRIMARY KEY (` id `) USING BTREE, 
The INDEX ` FK_ad1017e22d7c4817b0b4511470e ` (` payment `) USING BTREE, 
The INDEX ` FK_5ced2321ddd04917b6acd6fc5c6 ` (` type `) USING BTREE, 
The INDEX ` FK_fa6d9c980f1d4146bd3571a8274 ` (` wallet `) USING BTREE, 
The INDEX ` index_created_at ` (` created_at `) USING BTREE, 
The INDEX ` index_changed_amount ` (` changed_amount `) USING BTREE, 
The CONSTRAINT ` wallet_change_ibfk_1 ` FOREIGN KEY (` appointed `) REFERENCES ` commissions generated ` (` id `) ON DELETE RESTRICT ON the UPDATE RESTRICT, 
The CONSTRAINT ` wallet_change_ibfk_2 ` FOREIGN KEY (` type `) REFERENCES ` wallet_change_type ` (` id `) ON DELETE RESTRICT ON the UPDATE RESTRICT, 
The CONSTRAINT ` wallet_change_ibfk_3 ` FOREIGN KEY (` wallet `) REFERENCES ` wallets ` (` id `) ON DELETE RESTRICT ON the UPDATE RESTRICT, 
The CONSTRAINT ` wallet_change_ibfk_4 ` FOREIGN KEY (` order `) REFERENCES ` orders ` (` id `) ON DELETE RESTRICT ON the UPDATE RESTRICT 
) ENGINE=InnoDB AUTO_INCREMENT=5588657 CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic;

Now I need to request a total amount of several types of,

SELECT SUM (wh) changed_amount) AS totlaAmount
The FROM wallet_change wh
WHERE wh. Type IN (31,32,33,34,35,36,37,38,39,7,40,41,42,43) AND wallet=123

About 500 w, the entire table data is slow.. It's about 40 seconds,
Excuse me, how to optimize shout

CodePudding user response:

Increase the index type + + changed_amount wallet first, and then write, I can only give you write 2, other filling yourself,

Then
SELECT
(SELECT SUM (wh) changed_amount) AS totlaAmount
The FROM wallet_change wh
WHERE wh. Type=31 AND wallet=123)
+
(SELECT SUM (wh) changed_amount) AS totlaAmount
The FROM wallet_change wh
WHERE wh. Type=32 AND wallet=123) FROM DUAL;

CodePudding user response:

reference 1st floor AHUA1001 response:

increase index type + + changed_amount wallet first, and then write, I can only give you write 2, other filling yourself,

Then
SELECT
(SELECT SUM (wh) changed_amount) AS totlaAmount
The FROM wallet_change wh
WHERE wh. Type=31 AND wallet=123)
+
(SELECT SUM (wh) changed_amount) AS totlaAmount
The FROM wallet_change wh
WHERE wh. Type=32 AND wallet=123) FROM DUAL;

Type + wallet + changed_amount refers to the three fields added into an index of alliance? I am now three fields, there are indexed separately

CodePudding user response:

Three fields, combined index, the last is a field is changed_amount,
Type and wallet, which field difference is big, I put the field in the first,
For example, most people's name will not be repeated, and gender, men and women only two choices, if you want to use your name and gender index, because the name than the gender is big, the difference of the index name before, and gender in the back,

CodePudding user response:

Has no effect...

CodePudding user response:

Remember someone once said that a database is a metaphysics, sometimes can only rely on prayer,
I don't have your database here, just to watch you so described, also can only give you these opinions,

CodePudding user response:

Look at the first execution plan,,,

CodePudding user response:

Building a type + wallet composite index
Think of it, your database:
1. SELECT count (*) FROM wallet_change wh the WHERE wh. Type IN (31,32,33,34,35,36,37,38,39,7,40,41,42,43)
2. SELECT count (*) AS totlaAmount FROM wallet_change wh the WHERE wh. Wallet=123

1 and 2, which data?
If 1, use the
SELECT SUM (wh) changed_amount) AS totlaAmount
The FROM wallet_change wh
WHERE wallet=123 AND wh. Type IN (31,32,33,34,35,36,37,38,39,7,40,41,42,43)
If 2, use the
SELECT SUM (wh) changed_amount) AS totlaAmount
The FROM wallet_change wh
WHERE wh. Type IN (31,32,33,34,35,36,37,38,39,7,40,41,42,43) AND wallet=123

Also, need to consider the source of the 31,32,33,34,35,36,37,38,39,7,40,41,42,43, preferably in the table, to associate

CodePudding user response:

reference 7 floor generation princess reply:

built a type + wallet composite index
Think of it, your database:
1. SELECT count (*) FROM wallet_change wh the WHERE wh. Type IN (31,32,33,34,35,36,37,38,39,7,40,41,42,43)
2. SELECT count (*) AS totlaAmount FROM wallet_change wh the WHERE wh. Wallet=123

1 and 2, which data?
If 1, use the
SELECT SUM (wh) changed_amount) AS totlaAmount
The FROM wallet_change wh
WHERE wallet=123 AND wh. Type IN (31,32,33,34,35,36,37,38,39,7,40,41,42,43)
If 2, use the
SELECT SUM (wh) changed_amount) AS totlaAmount
The FROM wallet_change wh
WHERE wh. Type IN (31,32,33,34,35,36,37,38,39,7,40,41,42,43) AND wallet=123

Also, need to consider the source of the 31,32,33,34,35,36,37,38,39,7,40,41,42,43, preferably in the table, to associate

Is a foreign key link table, but I found by foreign key associated query seems more slowly, so only use of single table query

CodePudding user response:

null