MySQL Forums
Forum List  »  Newbie

[ NEED HELP ] - For optimize my select statement
Posted by: Vinicius Inuh
Date: May 07, 2015 03:57PM

Hello!

Dear Friends,

Im newbie on mysql and i have 02 tables:

table "CDR" = 37.000 records
and table "un_prefixo2" = more than 200.000 records

im waiting more less 20minutes for execute my select statement,

you can see down my select statement:

************************************

SELECT calldate AS DATA, src AS Origem, (CASE LENGTH(cdr.dst)
WHEN 8 THEN CONCAT('67',cdr.dst)
ELSE cdr.dst
END) AS Destino,un_prefixo2.prefixo AS Prefixo,billsec AS Duração, dstchannel AS Canal,prestadora AS Operadora,un_prefixo2.sigla_uf AS UF, un_prefixo2.nome_da_localidade AS Localidade,un_prefixo2.latitude AS Latitude,un_prefixo2.longitude AS Longitude, un_prefixo2.distancia AS Distancia, un_prefixo2.valor AS Valor
FROM cdr
INNER JOIN un_prefixo2
ON (IF(LENGTH(dst)>8,cdr.dst,CONCAT('67',cdr.dst)))
LIKE CONCAT('%',un_prefixo2.prefixo,'____')
WHERE calldate BETWEEN "2015-01-20%" AND "2015-02-10%"
AND billsec > 0
GROUP BY SUBSTR(uniqueid,1,10)
ORDER BY calldate DESC

************************************



how can lower this time?

I have created index however i need wait still long time

Thank you.

Att.
Vinicius

Options: ReplyQuote


Subject
Written By
Posted
[ NEED HELP ] - For optimize my select statement
May 07, 2015 03:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.