Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

Duplicados sobre 2.300.000 registros

Estas en el tema de Duplicados sobre 2.300.000 registros en el foro de Mysql en Foros del Web. 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 ...
  #1 (permalink)  
Antiguo 24/02/2011, 08:09
 
Fecha de Ingreso: julio-2006
Ubicación: Montevideo
Mensajes: 34
Antigüedad: 16 años, 6 meses
Puntos: 0
Duplicados sobre 2.300.000 registros

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?
  #2 (permalink)  
Antiguo 24/02/2011, 08:22
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 15 años, 2 meses
Puntos: 2658
Respuesta: Duplicados sobre 2.300.000 registros

¿Y cómo es la tabla? ¿Esos valores (Ana, rubia) son de campos distintos o es el mismo campo?
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #3 (permalink)  
Antiguo 24/02/2011, 08:28
 
Fecha de Ingreso: julio-2006
Ubicación: Montevideo
Mensajes: 34
Antigüedad: 16 años, 6 meses
Puntos: 0
Respuesta: Duplicados sobre 2.300.000 registros

como ejemplo tomemos que la tabla es asi:

Select nombre, pelo, edad from tabla
  #4 (permalink)  
Antiguo 24/02/2011, 08:31
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 15 años, 2 meses
Puntos: 2658
Respuesta: Duplicados sobre 2.300.000 registros

No se puede trabajar con "ejemplos" para darte algo eficiente.
Postea cómo es la tabla real.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #5 (permalink)  
Antiguo 24/02/2011, 08:39
 
Fecha de Ingreso: julio-2006
Ubicación: Montevideo
Mensajes: 34
Antigüedad: 16 años, 6 meses
Puntos: 0
Respuesta: Duplicados sobre 2.300.000 registros

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.
  #6 (permalink)  
Antiguo 24/02/2011, 08:58
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 15 años, 2 meses
Puntos: 2658
Respuesta: Duplicados sobre 2.300.000 registros

Prueba:
Código MySQL:
Ver original
  1. SELECT L1.country, L1.city
  2. FROM locations L1 INNER JOIN locations L2 ON L1.city = L2.city AND L1.id <> L2.id
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #7 (permalink)  
Antiguo 24/02/2011, 09:01
 
Fecha de Ingreso: julio-2006
Ubicación: Montevideo
Mensajes: 34
Antigüedad: 16 años, 6 meses
Puntos: 0
Respuesta: Duplicados sobre 2.300.000 registros

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?
  #8 (permalink)  
Antiguo 24/02/2011, 09:05
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 15 años, 2 meses
Puntos: 2658
Respuesta: Duplicados sobre 2.300.000 registros

Eso no afectaría el problema.
Acá el tema sería poner
Código MySQL:
Ver original
  1. 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...
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #9 (permalink)  
Antiguo 24/02/2011, 09:22
Colaborador
 
Fecha de Ingreso: enero-2007
Ubicación: México
Mensajes: 2.097
Antigüedad: 16 años
Puntos: 447
Respuesta: Duplicados sobre 2.300.000 registros

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
  #10 (permalink)  
Antiguo 24/02/2011, 09:32
 
Fecha de Ingreso: julio-2006
Ubicación: Montevideo
Mensajes: 34
Antigüedad: 16 años, 6 meses
Puntos: 0
Respuesta: Duplicados sobre 2.300.000 registros

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.
  #11 (permalink)  
Antiguo 24/02/2011, 09:48
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 15 años, 2 meses
Puntos: 2658
Respuesta: Duplicados sobre 2.300.000 registros

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:
Ver original
  1. EXPLAIN SELECT L1.country, L1.city
  2. 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.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #12 (permalink)  
Antiguo 24/02/2011, 10:03
 
Fecha de Ingreso: julio-2006
Ubicación: Montevideo
Mensajes: 34
Antigüedad: 16 años, 6 meses
Puntos: 0
Respuesta: Duplicados sobre 2.300.000 registros

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)?
  #13 (permalink)  
Antiguo 24/02/2011, 10:14
 
Fecha de Ingreso: julio-2006
Ubicación: Montevideo
Mensajes: 34
Antigüedad: 16 años, 6 meses
Puntos: 0
Respuesta: Duplicados sobre 2.300.000 registros

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.
  #14 (permalink)  
Antiguo 24/02/2011, 10:17
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 15 años, 2 meses
Puntos: 2658
Respuesta: Duplicados sobre 2.300.000 registros

Cita:
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?
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #15 (permalink)  
Antiguo 24/02/2011, 12:12
Colaborador
 
Fecha de Ingreso: enero-2007
Ubicación: México
Mensajes: 2.097
Antigüedad: 16 años
Puntos: 447
Respuesta: Duplicados sobre 2.300.000 registros

Cita:
Iniciado por Stendelis Ver Mensaje
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"

Etiquetas: duplicados, registros
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 17:23.