Performance problem in MySQL - Sum() and select is taking too long-CodePudding

i have a performance problem in MySQL.

I have a table with 215000 rows (InnoDB Engine) inserted in it and to execute the function SUM() on one column for only 1254 rows is taking 500ms.

The version i am using is : MySQL 5.7.32

The computer specs are the following:

Core I5 3.0 Ghz
8 Gb Ram
Solid State Drive

Here i leave information about the structure of the database:

mysql> select count(*) from cuenta_corriente;
 ---------- 
| count(*) |
 ---------- 
|   214514 |
 ---------- 

mysql> describe cuenta_corriente;
 ----------------------- ------------- ------ ----- --------- ---------------- 
| Field                 | Type        | Null | Key | Default | Extra          |
 ----------------------- ------------- ------ ----- --------- ---------------- 
| ID_CUENTA_CORRIENTE   | int(11)     | NO   | PRI | NULL    | auto_increment |
| ID_CLIENTE            | int(11)     | YES  | MUL | NULL    |                |
| ID_PROVEEDOR          | int(11)     | YES  | MUL | NULL    |                |
| FECHA                 | varchar(50) | YES  | MUL | NULL    |                |
| FECHA_FISCAL          | varchar(50) | YES  |     | NULL    |                |
| ID_OPERACION          | int(11)     | YES  | MUL | NULL    |                |
| ID_TIPO_OPERACION     | int(11)     | YES  | MUL | NULL    |                |
| DEBE                  | double      | YES  |     | 0       |                |
| HABER                 | double      | YES  |     | 0       |                |
| TOTAL                 | double      | YES  |     | 0       |                |
| SALDO_ANTERIOR        | double      | YES  |     | 0       |                |
| SALDO_ACTUAL          | double      | YES  |     | 0       |                |
| ID_OPERACION_ASOCIADA | int(11)     | YES  |     | NULL    |                |
| ELIMINADO             | int(11)     | YES  |     | 0       |                |
| ID_EMPLEADO           | int(11)     | NO   |     | 0       |                |
 ----------------------- ------------- ------ ----- --------- ---------------- 

show indexes from cuenta_corriente;

 ------------ ------------------------------------------------------------ 
| Non_unique | Key_name                               Column_name         |
 ------------ ------------------------------------------------------------ 
|          0 | PRIMARY                                ID_CUENTA_CORRIENTE |
|          1 | IDX_CUENTA_CORRIENTE                   ID_CLIENTE          |
|          1 | IX_cuenta_corriente_FECHA              FECHA               |
|          1 | IX_cuenta_corriente_ID_CLIENTE         ID_CLIENTE          |
|          1 | IX_cuenta_corriente_ID_PROVEEDOR       ID_PROVEEDOR        |
|          1 | IX_cuenta_corriente_ID_TIPO_OPERACION  ID_TIPO_OPERACION   |
|          1 | IX_cuenta_corriente_ID_OPERACION       ID_OPERACION        |
|          1 | IDX_cuenta_corriente_ID_OPERACION      ID_OPERACION        |
 ------------ ------------------------------------------------------------ 
8 rows in set (0.00 sec)

The problem is with the folowing queries, in my opinion they are taking too long, considering that i have an index for the column ID_CLIENTE and that there are only 1254 rows with the ID_CLIENTE column = 19. Here are the query results:

mysql> SELECT COUNT(*) FROM CUENTA_CORRIENTE WHERE ID_CLIENTE = 19;
1254 rows

mysql> SELECT DEBE FROM CUENTA_CORRIENTE WHERE ID_CLIENTE = 19;
1254 rows - 0.513 sec

mysql> SELECT SUM(DEBE) FROM CUENTA_CORRIENTE WHERE ID_CLIENTE = 19;
0.582 sec

The strange thing is if i select all the columns instead selecting only the "DEBE" column, it takes less time:

mysql> SELECT * FROM CUENTA_CORRIENTE WHERE ID_CLIENTE = 19;
0.095 sec

Can anyone help me to improve the performance?

CodePudding user response：

You can make just that query fast by creating a composite index to support it.

ie:
CREATE INDEX IDX_QUERY_FAST ON cuenta_corriente (ID_CLIENTE, DEBE)

But don't forget, each index has to be maintained, so it slows down any inserts into the table, so you don't want 200 indexes supporting every possible query.

With the existing index, the engine should be smart enough to identify the 1200 rows you care about using the index, but then it has to go read all the table records (across however many pages) that have the individual rows to get the DEBE column.

CodePudding user response：

Add this index to help most of the queries you have shown:

INDEX(ID_CLIENTE, DEBE)

and drop INDEX(ID_CLIENTE) if you have such.

Are all of your secondary indexes starting with the PRIMARY KEY?? (The names imply such; please provide SHOW CREATE TABLE to definitively say what columns are in each index.) Don't start an index with the PK; it is likely to be useless.

Run EXPLAIN SELECT ... to see which index a query uses.

When timing a query, run it twice. The first run may spend extra time loading index or data rows into cache (in RAM in the buffer_pool); the second run may be significantly faster because of the caching.