Slow problem with CAST in consultation

Question

I need a help in a data conversion that I imagine is the problem of slowness in a query of mine. I am using Mysql 5.6. In the second query below, in the return of the MRESULT variable, when I use the value of @CALL_ID_DC as a parameter, the query runs very fast. When using the variable itself @CALL_ID_DC, the query wheel very slow. The field idCall is a VARCHAR(255).

How should I correct this problem?

SET @CALL_ID_DC = (
                             SELECT DC.CallId AS idCall
                             FROM TABELA_DC DC
                             WHERE 
                                DC.Seq = 370065764
                        );

-- @CALL_ID_DC => está com valor '12345'


SET @MRESULT = (
                        SELECT C.idCall FROM TABELA_C C
                        WHERE
                            C.idCall = @CALL_ID_DC -- Usando assim, fica lenta a consulta
                            -- C.idCall = '12345' -- Usando assim, a consulta fica rápida
                        LIMIT 1
                    );

I tried to do the variable CAST @CALL_ID_DC for CHAR, but did not change the behavior of the query.

Answer 1

If your database is set to Innodb. You could create an INDEX for table/field. Thus the database will index the table and the queries filtering that/those fields would be faster.

Note: This guideline is especially important for Innodb tables, where the primary key determines the physical layout of the lines in the data file.

CREATE INDEX allows adding indexes to existing tables.

Ex: CREATE INDEX idx_CLIENTES_CODIGO ON CLIENTS(Code);

Usually used to optimize queries.

Follow a link to read link