Mysql does not use Input in query (Inner Join)

Question

I have a query that is taking time to be executed, analyzing the explain I see that Mysql is not using the Word in one of the tables.

Tables:

CREATE TABLE `rel_financeiro` (
  `protocolo` char(13) NOT NULL,
  `aceito` datetime DEFAULT NULL,
  `processado` datetime DEFAULT NULL,
  `valor_previsto` decimal(10,2) NOT NULL DEFAULT '0.00',
  `valor_pago` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`registro`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `emails` (
  `id` char(36) NOT NULL,
  `data` datetime NOT NULL,
  `de` varchar(200) NOT NULL,
  `para` varchar(200) NOT NULL,
  `protocolo` varchar(13) DEFAULT NULL,
  `assunto` varchar(255) NOT NULL,
  `pasta` varchar(60) NOT NULL,
  `lido` tinyint(1) NOT NULL DEFAULT '0',
  `headers` text NOT NULL,
  `mensagem` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `protocolo` (`protocolo`),
  KEY `pasta` (`pasta`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query:

SELECT r.protocolo, r.aceito, r.valor_previsto
  FROM rel_financeiro r
 INNER JOIN emails e ON r.protocolo = e.protocolo
 WHERE e.id IN ('e665a3e5-098f-a754-d4fe-5602a15aa191', '39b191b3-8f04-11e5-b9b8-040166406e01', '3e6c68ee-8f04-11e5-b9b8-040166406e01');

Explain result:

+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows    | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+
|  1 | SIMPLE      | e     | const | PRIMARY,protocolo | PRIMARY | 108     | const |       1 |             |
|  1 | SIMPLE      | r     | ALL   | NULL              | NULL    | NULL    | NULL  | 5197139 | Using where |
+----+-------------+-------+-------+-------------------+---------+---------+-------+---------+-------------+

I’ve tried so many different ways and so far nothing.

Answer 1

Your create table is setting a primary key with a column that does not exist, registro. Maybe the intention was

  PRIMARY KEY (`protocolo`)

If the desired primary key is the one already defined, create an index:

  KEY (`protocolo`)

or single index

  UNIQUE (`protocolo`)

As appropriate.

Answer 2

Assuming protocolo be the PK of rel_financeiro as pointed out in reply of fbiazi, the problem seems to be the position of the Join:

SELECT r.protocolo, r.aceito, r.valor_previsto
  FROM emails e 
 INNER JOIN rel_financeiro r ON r.protocolo = e.protocolo 
 WHERE e.id IN ('e665a3e5-098f-a754-d4fe-5602a15aa191', '39b191b3-8f04-11e5-b9b8-040166406e01', '3e6c68ee-8f04-11e5-b9b8-040166406e01'); 

You want the from be the table emails for the clause where use the index of id and reduce emails for a small data set. After that the join can use the index in protocolo.

Of course the optimizer of darlings could do his part and reach a similar conclusion alone, but there are no guarantees in this case. See the result I received with his query original:

Mysql is being very literal, bringing all the financial relay to memory first and only then trying to do the join of the result with the emails.

---

Functional example in SQL Fiddle