Optimize SELECT DISTINCT CONCAT query in MySQL

Question

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?

Michael Buen · Accepted Answer · 2009-07-01 01:54:28Z

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

answered Jul 1, 2009 at 1:54

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Seth Over a year ago

If you put the sub-select in a temporary table first, you can shave off some seconds: create temporary table temp1 SELECT ALFA_CLAVE, FECHA_NACI FROM listado GROUP ALFA_CLAVE, FECHA_NACI HAVING COUNT(*) > 1; select concat(alfa_clave, fecha_naci) from temp1;

Collectives™ on Stack Overflow

Optimize SELECT DISTINCT CONCAT query in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related