Newsgroup: it.comp.software.database
Titolo: [lungo e propedeutico] non riesco a uscire da questo empasse

premetto, è da ieri alle 2 di notte che ci sono su e ho dormito solo 4
ore :)
la struttura della tabella è questa:
CREATE TABLE `world_cities3` (
  `id` int(10) unsigned NOT NULL default '0',
  `code` char(2) default NULL,
  `city_name` varchar(100) default NULL,
  `city_name_accent` varchar(100) default NULL,
  `state_region` char(3) default NULL,
  `population` bigint(20) default NULL,
  `latitude` float NOT NULL default '0',
  `longitude` float default NULL,
  PRIMARY KEY  (`id`),
  KEY `code` (`code`),
  KEY `latitude` (`latitude`)
) TYPE=MyISAM;
ci sono circa 3 milioni di righe, molte di queste righe sono doppioni (non
referenziati complimentoni) 
specifico "doppioni"
turin = torino
julia augusta taurinorum = torino
ecco, la media è che ogni città ha circa dai 4 nomi diversi.
ora a me interesserebbe creare una tabella temporanea per poter eliminare le
centinaia di migliaia di doppioni in un secondo momento del tipo:
CREATE TABLE `city_aliases3` (
  `id_main` int(10) unsigned NOT NULL default '0',
  `id_child` int(10) unsigned NOT NULL default '0',
  `city_name` varchar(255) NOT NULL default '',
  `population` bigint(20) unsigned default '0',
  `city_name_accent` varchar(255) NOT NULL default ''
) TYPE=MyISAM;
volendo popolare city_aliases3 con i dati da world_city3 la mia query
risulta essere questa:
SELECT * FROM world_cities2
WHERE
ROUND(latitude,5) = ".number_format($record['latitude'],5,".",".")."
AND code = '".$record['code']."'
che mi dà una discreta sicurezza di trovare i doppioni (code = stato a cui
appartiene la città, latitude è un float)
come avrete capito sto eseguendo tutto dal php, ma stime ottimistiche danno
circa 4.5 giorni per la fine...
dunque, sono sicuro che in realtà basti una query ben assettata da console
mysql, ma i miei tentativi erano piuttosto ridicoli...
qui sotto il mio tentativo(ridicolo direi) col php, che pare funzionare ma
ci sta mettendo una vita...
<?php
error_reporting(E_ALL);
require("install.php");
$inizia = date("H:i:s");
print("La query è iniziata alle: ".$inizia);
$start = $_GET['start'];
$end   = $start + 100;
$SQL = "
SELECT * FROM world_cities2
LIMIT ".$start.", 100
";
// print("\nCiclo: ".$SQL."\n");
//sleep(1);
$query = mysql_query($SQL) or die("not able");
while($record = mysql_fetch_array($query)){
$SQL_2 = "SELECT * FROM city_aliases2 WHERE (id_main = ".$record['id']." OR
id_child = ".$record['id']." )";
//print("guardo se è già inserita: ".$SQL_2."\n");
$query2 = mysql_query($SQL_2);
$conta  = mysql_num_rows($query2);
if($conta == 0){
$SQL_3 = "
SELECT * FROM world_cities2
WHERE
ROUND(latitude,5) = ".number_format($record['latitude'],5,".",".")."
AND code = '".$record['code']."'
";
//print("\nSeleziono con parametri".$SQL_3."\n");
//exit;
$query3 = mysql_query($SQL_3);
while($record3 = mysql_fetch_array($query3)){
$SQL_5 = "SELECT * FROM city_aliases2 WHERE id_child = ".$record3['id']." ";
//print("\nSecondo Check ".$SQL_5."\n");
$query5 = mysql_query($SQL_5);
$conta5  = mysql_num_rows($query5);
if($conta5 == 0){
$SQL_4 = "INSERT INTO 
city_aliases2 (id_main, id_child, city_name, population, city_name_accent)
VALUES (".$record['id'].", ".$record3['id'].", 
'".addslashes($record3['city_name'])."', 
".(int)$record3['population'].", 
'".addslashes($record3['city_name_accent'])."')";
$query4 = mysql_query($SQL_4) or die("unable: $SQL_4\n".mysql_error());
//print("\n Inserisco l'alias".$SQL_4."\n");
/*if($record['id'] != $record3['id']){
$SQL_6 = "DELETE FROM world_cities2 WHERE id = ".$record3['id']." ";
$query6 = mysql_query($SQL_6);
//print("\nCancello dal vecchio\n".$SQL_6."\n");
}
*/
}
}
}
}
//mysql_free_result($query3);
//mysql_close();
//header("location: rizacazzi.php?start=".$end);
//exit;
$fine = date("H:i:s");
print("\n\nLa query è finita alle: ".$fine);
if($end <  3036070 ){
?>
</pre>
<script>
window.location='dapiangere.php?start=<?php print($end);?>';
</script>
<?php
}
else{
print("Dovrei aver finito");
}
?>
-- 
vieni, facciamo ancora un'altra foto col colombo in mano.
sarai contenta quando poi tua cugina lo vedrà 
che a venezia siamo stati anche noi...

e soprattutto, mysql è adatto a questo genere di manipolazione?
conviene forse farlo da postgresql?
grazie

gli alias questi sconosciuti
INSERT INTO city_aliases 
(SELECT
world_cities2.id
 AS id_main,
xx.id
 AS id_child
FROM world_cities2
INNER JOIN world_cities2 xx ON xx.latitude = world_cities2.latitude
AND xx.code = world_cities2.code
AND 
world_cities2.id
 <> 
xx.id
)
dite che è giusto?
poi trovo il modo di portarmi dietro i dati che mi servono tipo population
(non si sa a priori quale record ha la population)

 AND 
world_cities2.id
 <> 
xx.id
)
niente, sono disperato... sulla carta funziona, se uso una tabella con 500
record è una scheggia, se uso la tabella intera non funziona più.
se metto un limit 0,100 mi fa aspettare per sempre.
c'è qualche guru disposto ad aiutarmi?
grazie
-- 
chmod -R a-w The\ untouchables.avi

 se metto un limit 0,100 mi fa aspettare per sempre.
ok, il limit è stata una idea barbina. ho lasciato andare il tutto.
è pazzesco:
mysql> insert into city_aliases  (SELECT 
world_cities2.id
 AS id_main, 
xx.id
AS id_child FROM world_cities2 INNER JOIN world_cities2 xx ON xx.latitude =
world_cities2.latitude AND xx.code = world_cities2.code);
Query OK, 169528551 rows affected (58 min 7.79 sec)
Records: 169528551  Duplicates: 0  Warnings: 0
169.528.551 record, vuol dire che ogni città è scritta in circa 55 modi
diversi. ora cerco di capire quale città crea tutto questo casino.
anche se non risponde nessuno, vado avanti lo stesso.


record è una scheggia, se uso la tabella intera non funziona più.
Crea la tabella CITTA e quella ALIASES.
Seleziona gli stati. Saranno un 3-400.
Per ogni stato:
1. Seleziona le citta' di quello stato da WORLD_CITIES3 dentro una
   tabella temporanea TEMP.
2. Fai il lavoro che dici che e' una scheggia sulla tabella TEMP e
   ricavane le due minitabelle MINICITTA e MINIALIAS.
3. INSERT INTO CITTA SELECT * FROM MINICITTA;
   INSERT INTO ALIASES SELECT * FROM MINIALIAS;
4. Svuota le due minitabelle e TEMP.
5. Ritorna al passo 1 col prossimo stato.
Vorra' dire che ci impieghi 3-400 schegge :-)
Leonardo divide et impera
-- 
Nach doiligh domhsa mo chailin a mholadh
'S ni he amhain mar bhi si or
Bhi si mar gath greine a dhul in eadan na gloinne
Is bhi sceimh mhna na finne le mo cailin or