Duplicados sobre 2.300.000 registros

[Stendelis]

Hola no doy con el select correcto para poder saber los duplicados en esta tabla de esta manera.

Ana rubia 10
Ana rubia 20
Ana rubia 14
Ana peliroja 22
Ana morocha 21
Lina rubia 10
Lina rubia 20

Necesito un select que me devuelva esto

Ana rubia
Lina rubia

Intente con select in, diferentes distinct y group by, pero no doy con la correcta o me demora mucho y me cancela el proceso.

Alguien tiene una solución?

[gnzsoloyo]

¿Y cómo es la tabla? ¿Esos valores (Ana, rubia) son de campos distintos o es el mismo campo?

[Stendelis]

como ejemplo tomemos que la tabla es asi:

Select nombre, pelo, edad from tabla

[gnzsoloyo]

No se puede trabajar con "ejemplos" para darte algo eficiente.
Postea cómo es la tabla real.

[Stendelis]

Muy bien aquí va:

CREATE TABLE IF NOT EXISTS `locations` (
`id` bigint() NOT NULL AUTO_INCREMENT,
`country` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
`city` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
`locationname` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ;

En esta tabla hay muchas localidades o puntos geográficos, y hay muchas ciudades (city) que tienen el mismo nombre en diferentes países, necesito un select que me liste por ejemplo:

country | city

Pakistan | Gaza
Mozambique | Gaza


Tener en cuenta de que los registros con country Pakistan o Mozambique que tienen a Gaza como city son muchos, yo solo necesito detectar que country tiene city duplicado con otro country.

[gnzsoloyo]

Prueba:

Código MySQL:

SELECT L1.country, L1.city
FROM locations L1 INNER JOIN locations L2 ON L1.city = L2.city AND L1.id <> L2.id
 

[Stendelis]

me sale un error con esto,

#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

el select debe de estar bien, puedo hacer varios selects poniendo un límite?

[gnzsoloyo]

Eso no afectaría el problema.
Acá el tema sería poner

Código MySQL:

SET SQL_BIG_SELECTS=1;
 

Antes d ejecutar la consulta.
De todos modos se va a llevar algo de tiempo.
Tengo otras idas, pero son un poquito máscomplejas, aunque pueden ser rápidas...

[leonardo_josue]

Hola Stendelis:

La idea del compañero gnzsoloyo me parece bastante óptima, hay algunas otras formas de obtener el mismo resultado, pero debido a las dimensiones de tu tabla puede que sean más lentas, pues involucran subconsultas y agrupaciones. Por ejemplo, con los datos que ponías de los nombres y el pelo obtuve una consulta algo compleja pero que funciona igual y la dejo por si la quieres probar, pero sigo creyendo que la lógica de gnzsoloyo es correcta

Código:

select distinct T1.country, T1.city from locations T1 inner join 
(select city, (select count(distinct country) from locations T2 where T2.city = T3.city) total 
from locations T3 
group by T3.city having count(*) > 1) T4 on T1.city = T4.city and T4.total > 1

Saludos
Leo

[Stendelis]

Nop, al pasar un par de minutos el MySql se libera, pero no me devuelve nada ni mensaje de error.

Como puedo verlo o como podría hacer que el select final sea un poco mas chico.

[gnzsoloyo]

Crea un índice sobre el campo `city`, eso puede ayudar un poco a resolver el problema.
Si no hay diferencia, hay que forzarlo a usarla.

Procura usar EXPLAIN para ver el resultado del parser:

Código MySQL:

EXPLAIN SELECT L1.country, L1.city
FROM locations L1 INNER JOIN locations L2 ON L1.city = L2.city AND L1.id <> L2.id
 

De ese modo podremos ver por dónde está fallando la consulta.

[Stendelis]

Generé el indice en city.

El Explain me da todo on, sin errores, lo mas seguro es que el MySql debe de tener timeout y me devuelve a la página inicial de PhpmyAdmin sin mostrarme nada.

Puedo colocar que haga este select con los primeros 10.000 registros (id >0 and id <10000)?

[Stendelis]

listo el select de leonardo_josue funcionó, perfectamente.

select distinct T1.country, T1.city from locations T1 inner join
(select city, (select count(distinct country) from locations T2 where T2.city = T3.city) total
from locations T3
group by T3.city having count(*) > 1) T4 on T1.city = T4.city and T4.total > 1

Son unos grandes cualquiera de los dos.

Muchas gracias a ambos.

[gnzsoloyo]

El Explain me da todo on, sin errores, lo mas seguro es que el MySql debe de tener timeout y me devuelve a la página inicial de PhpmyAdmin sin mostrarme nada.

¿A qué te refieres con "todo on"?

EXPLAIN nos puede dar información que ayude a mejorar la performance de la consulta, ya que nos explica cómo está haciendo para obtener datos y aporta alguna que otra pista importante. ¿Por qué no posteas el resultado?

[leonardo_josue]

Iniciado por Stendelis

listo el select de leonardo_josue funcionó, perfectamente.

Un gusto haber sido de ayuda, no sé qué tanto tardó la consulta en regresar algún resultado, igual y hay alguna otra forma de hacerlo más eficiente, pero bueno, eso sería la cereza en el pastel jejeje... Lo importante es que te ayudó a resolver tu problema no???

Saludos
Leo.

--------------------------------------
"Si quieres resultados distintos no hagas siempre lo mismo"