How to reduce the response time of a query that yields ~4.9 million rows in ~12-13 seconds

Question

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.

Answer 1

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

To convert the query to a covering index