0

I have the following doubt about the execution performance of a query. This query takes about 12-13 seconds, and brings a total of 4,879,049 rows. Could I lessen this query's response time?

The query at issue is the following one:

  SELECT id_empresa,
         referencia,
         archivo,
         status 
    FROM Outbound_call_files 
   WHERE movido=0 
     AND status NOT IN('Peticion') 
ORDER BY id_empresa, status;

The execution plan is:

| id | select_type | table               | type | possible_keys | key           | key_len | ref   | rows    | Extra       |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+---------+-------------+
|  1 | SIMPLE      | Outbound_call_files | ref  | motivo_status | motivo_status | 1       | const | 2561294 | Using where |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+-

SHOW CREATE INDEX returns the following:

| Outbound_call_files |          1 | motivo_status      |            1 | movido      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Outbound_call_files |          1 | motivo_status      |            2 | id_empresa  | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Outbound_call_files |          1 | motivo_status      |            3 | status      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |

I can see the cardinality is too low.

The structure and constraints of this table are shown below:

CREATE TABLE `Outbound_call_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_empresa` int(11) NOT NULL,
  `id_campana` int(11) NOT NULL,
  `referencia` text NOT NULL,
  `movido` tinyint(4) NOT NULL,
  `archivo` varchar(50) NOT NULL,
  `status` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_empresa_campana` (`id_empresa`,`id_campana`),
  KEY `motivo_status` (`movido`,`id_empresa`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=10144111 DEFAULT CHARSET=latin1

Executing profiling, this was the result:

SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000070 |
| checking permissions |  0.000008 |
| Opening tables       |  0.000019 |
| System lock          |  0.000010 |
| init                 |  0.000030 |
| optimizing           |  0.000018 |
| statistics           |  0.000090 |
| preparing            |  0.000022 |
| executing            |  0.000004 |
| Sorting result       |  0.000006 |
| Sending data         | 12.512944 |
| end                  |  0.000017 |
| query end            |  0.000006 |
| closing tables       |  0.000011 |
| freeing items        |  0.000019 |
| logging slow query   |  0.000004 |
| logging slow query   |  0.000067 |
| cleaning up          |  0.000006 |
+----------------------+-----------+

Note: The database server has 150G RAM and the buffer pool is configured in 100G.

  • 2
    What use case requires retrieving 4 million rows to the client? Please also consider formatting your question properly. – mustaccio Jul 25 at 17:30
  • 1
    Im optimizing all slow queries into slow query log file and this query is one of them. I dont undestand logic behind from this query but as dba my dutty is optimize and improve the mess that the developers does – Iori_Yagami Jul 25 at 17:42
  • 1
    totally i agree with you but if the mess was done before I was and now you were requested to correct the mess – Iori_Yagami Jul 25 at 18:32
  • 3
    If your bottleneck is network then the solution is to reduce the data size being sent or increase the pipe size. There's no magic trick to circumvent this. – Jacob H Jul 25 at 18:36
  • 1
    What are the possible value of status? How many of each? SELECT status, count(*) FROM Outbound_call_files GROUP BY status; – Rick James Jul 26 at 1:34
-1

Create a new index that is based on movido,status, id_empresa, referencia, archivo

To convert the query to a covering index

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.