Optimize SELECT DISTINCT CONCAT query in MySQL

Question

Hello!

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?

Thanks in advanced

Answer 1

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

Answer 2

it's great i was tried, and ok.