MySQL does not use the index in the query (inner join)

Question

MySQL does not use the index in the query (inner join)

Navigation

#1 by (3 votes)
#2 by (3 votes)

5

I have a query that is taking a while to execute, analyzing explain I see that MySQL is not using the index 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 have tried in many different ways and so far nothing.

mysql

asked by anonymous 19.11.2015 / 22:00

2 answers

Problem with Nuget Packages Visual Studio Help with RGB color in 16bit Hexadecimal

score 3 · 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 that is already defined, create an index:

  KEY ('protocolo')

or unique index

  UNIQUE ('protocolo')

As appropriate.

score 3 · Answer 2

Assuming that protocolo is the PK of rel_financeiro as pointed out in answer 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');

Youwantfromtobetheemailstablesothatthewhereclauseusestheidindexandreducesemailstoasmallsetofdata.Afterthat,thejoincanusetheindexasprotocolo.

Ofcourse,thequeriesoptimizercoulddothepartofitandcometoasimilarconclusiononitsown,buttherearenoguaranteesinthatcase.SeetheresultIgotwithyouroriginalquery:

MySQLisquiteliteral,bringingallthefinancial_relatedtomemoryfirstandonlythentryingtojoinoftheresultwithemails.

Functional example in SQL Fiddle