Search code examples
mysqlsqlsql-optimization

How can I improve the performance of this query?

In this query I make a "left join" with a table (ASPECT_CONTACT_EVENTS) that have many registers (In this period, 250.000).

The table Promessas have a 200 registes in this period.

SET @inicio = '2015-02-24 00:00:00';

SET @fim = '2015-02-24 23:59:00';

SELECT Promessas.Data_acordo, if(Ativos.account_number IS NULL,
IF(Promessas.tipo_acordo='A VISTA',"11-QTDE DE PROMESSAS A VISTA (RECEPTIVO)","12-QTDE DE PROMESSAS PARCELADO (RECEPTIVO)"),
IF(Promessas.tipo_acordo='A VISTA',"05-QTDE DE PROMESSAS A VISTA (ATIVO)","06-QTDE DE PROMESSAS PARCELADO (ATIVO)"))  AS TIPO,

COUNT(DISTINCT Promessas.id_contr), Promessas.cluster FROM DWH_RBZ.tb_itau_banco_promessa_dia as Promessas
left join DWH_RBZ.ASPECT_CONTACT_EVENTS AS Ativos on ((Ativos.account_number = Promessas.id_contr) and (time_of_contact between (@inicio) and (@fim)) )
Where Promessas.data_acordo between (@inicio) and (@fim)
Group By tipo, Promessas.cluster;

How can I improve this performance?

Solution

  • The optimizer does not do well with @variables. Please put the values directly into the query and see if that speeds up the query.

    Otherwise, Promessas needs an index starting with data_acordo in hopes that the WHERE clause can use the index. The second table, Ativos needs a compound INDEX(account_number, time_of_contact), assuming that times_of_contact is in that table. Please use aliases on all fields!