CODEWITHSUNDEEP
mysqloptimization
L. Cosio
L. Cosio

Reputation: 227

Optimize SELECT DISTINCT CONCAT query in MySQL

I'm running this query:

SELECT DISTINCT CONCAT(ALFA_CLAVE, FECHA_NACI) FROM listado GROUP BY ALFA_CLAVE HAVING count(CONCAT(ALFA_CLAVE, FECHA_NACI)) > 1

Is there any way to optimize it? Queries are taking 2-3 hours on a table with 850,000 rows.

Adding an index to ALFA_CLAVE and FECHA_NACI would work?

Upvotes: 1

Views: 5418

Answers (1)

Michael Buen
Michael Buen

Reputation: 39423

mysql don't have functional/expression index capability, it cannot put an index on a function/expression.

try it this way, i.e. concat them later:

select concat(x.alfa_clave, x.fecha_naci) 
from
(
    SELECT ALFA_CLAVE, FECHA_NACI 
    FROM listado 
    GROUP ALFA_CLAVE, FECHA_NACI 
    HAVING COUNT(*) > 1
) as x

Upvotes: 1

Related Questions