Newsgroup: it.comp.software.database
Titolo: ricerca con % su ~tre milioni di righe.

Ciao, ho un database con tutte le città del mondo (se vi serve vi dico dove
scaricarlo, ha anche le posizioni geo) mi interesserebbe fare delle
ricerche sul campo city che è già un indice ovviamente.
solo che ad esempio se scrivo tur% ci mette 4/5 secondi (2389 record).
esiste un modo migliore per fare delle ricerche di questo tipo?
ho avuto un'idea bislaccca, (e immagino che mi prenderete in giro per
questo)
se creassi una tabella per ogni lettera dell'alfabeto
cities_a
cities_b
[...]
ne trarrei giovamento?
non so come fare altrimenti...
grazie per eventuali consigli. 
-- 
chmod -R a-rwx The\ untouchables

 ha scritto nel messaggio 
news:eWL9f.12197$hC1....@tornado.fastwebnet.it...


 mi potresti dire dove scaricarlo ?
e un aiutino alla mia domanda almeno?
http://del.icio.us/sathia/world
;)
-- 
chmod -R a-w The\ untouchables

> e un aiutino alla mia domanda almeno?
guarda, ci sono due casi:
a) fai una ricerca del tipo  xyz%
e allora guadagni, perchè la sapendo già l'inziale fai una ricerca 
approssimativamente su un numero di record pari a 1/24simo.
b) fai una ricerca del tipo %xyz o %xyz&
e allora non guadagni, perchè comunque si fa le tabelle dall'a alla z ;)
una sola cosa, in che formato l'hai importato quel file di testo?
se l'hai importanto in mysql, mi potresti spiegare che istruzioni hai usato 
con INFILE ?
grazie e ciao!!! 

 grazie e ciao!!!


 usato con INFILE ?
nota che prima di parsarlo ho fatto un replace da vim 
dei tab in ;
:% s/\t/;/g
tabella
CREATE TABLE `cities` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `Country` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `Accent City` varchar(255) NOT NULL default '',
  `Region` varchar(255) NOT NULL default '',
  `latitude` varchar(255) NOT NULL default '',
  `longitude` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `Country` (`Country`,`city`)
) 
insert.php
<pre>
<?php
#error_reporting(E_ALL);
$db_host = "localhost";
$db_user = "user";
$db_password = "pass";
$db_name = "db";
$db = mysql_connect($db_host, $db_user, $db_password);
if ($db == FALSE)
die ("database errato");
mysql_select_db($db_name, $db)
or die ("Connessione al database errata");
$f = "/home/sathia/Desktop/cities.txt";
 $lines = file($f);
   foreach ($lines as $value) {
    $part = explode(";",$value); 
    foreach($part as $val){
        $partx .= "'".addslashes($val)."', ";    
    }
    $partx = substr($partx,0,-2);
    $SQL = "INSERT INTO cities VALUES (".$partx.")\n";
    $partx = NULL;
    //print($SQL);
    //print($i++."\n");
    mysql_query($SQL)or die(mysql_error());
   }
?>


 se lo trovo lo posto
ti prego recupera quel. file perchè sto impazzendo :D

 dei tab in ;
 e come si fa?:D
perdona l'ignoranza :(

 ora è più chiaro ?
a grazie, ora ho capito.
ero partito dal presupposto che l'idea di splittare le tabelle fosse troppo
idiota!
non cercherò mai %oma solo %rom
farò dei test, anche se non mi sembra una cosa così furba
btw, ti ho postato il file php per tirare su il db.
ciaociao


giuseppe wrote:

hai vim?
:% s/\t/;/g
schiacci : e poi copi esattamente dal : in poi
a quel punto puoi andare a farti un the e anche cena.
ciao ciao



Che bisogno c'era di fare sostituzioni?
si puo' fare tutto da  SQL:
drop table if exists world_cities2;
create table world_cities2 (
   id int not null auto_increment primary key,
   Country_Code varchar(30),
   City_Name varchar(100),
   Accent_City_Name varchar(100),
   State_Region varchar(100),
   Population bigint,
   Latitude float,
   Longitude float,
   key (City_Name)
);
load data local infile 'cities.txt' into table world_cities2
ignore 1 lines
(Country_Code, City_Name, Accent_City_Name, State_Region, Population, Latitude, Longitude)
;
E il motivo per cui la query e' lenta e' che hai un indice composto, ma usi solo
la seconda parte dell'indice. Prova a mettere un indice solo su citta' (oppure invertilo,
e metti citta+ nazione) e vedi che la velocita' cambia:
  select count(*) from world_cities2 where city_name like 'ver%';
+----------+
| count(*) |
+----------+
|     6216 |
+----------+
1 row in set (0.02 sec)
select count(*) from world_cities2 where city_name like 'tur%';
+----------+
| count(*) |
+----------+
|     2849 |
+----------+
1 row in set (0.02 sec)
select count(*) from world_cities2 where city_name like 'ro%';
+----------+
| count(*) |
+----------+
|    17679 |
+----------+
1 row in set (0.05 sec)
ciao
gmax
-- 
  _  _ _  _
(_|| | |(_|><
  _|
http://gmax.oltrelinux.com

 
Vim non e' fatto per questo tipo di lavoro. Non con un file di 138 MB.
Se proprio devi fare la sostituzione, e vuoi evitarti quel te', usa Perl:
$ time perl -i.bak -pe 's/\t/;/g' cities.txt
real    0m19.997s
user    0m18.900s
sys     0m0.850s


 cambia:
avevo pensato a questa cosa, ma poi mi sono detto che sarebbe stata la
stessa cosa. ho sbagliato :)
grazie mille!


giovani, ho windows, quindi non ho ne vim ne perl :(:(:(
come cacchio facciooooooooooooooooo 

 
gmax, mi sento veramente un cane :/
grazie per i preziosissimi consigli!


 1 row in set (0.02 sec)
Showing rows 0 - 29 (2849 total, Query took 0.0163 sec)
ti ringrazio infinitamente
Sat_


 
A parte il fatto che non e' necessario fare la sostituzione,
(vedi l'altro post) su Windows non e' vietato installare Vim e Perl.
http://www.vim.org/download.php#pc
http://www.activestate.com/Products/ActivePerl/


proprio non lo sapevo....
sto scaricando l'msi, poi eseguo l'istruzione che hai postato tu dal cmd ?
"Giuseppe Maxia" <gmax_@_cpan_._org> ha scritto nel messaggio 
news:4367a67f$0$24635$4faf...@reader3.news.tin.it...


> 
real    0m12.481s
user    0m9.222s
sys     0m0.885s
quanta potenza sprecata nelle mie mani... ;)


 esiste un modo migliore per fare delle ricerche di questo tipo?
da quello che ne so, tutte le ricerche per LIKE disabilitano
eventuali indici sul campo. Inoltre, una divisione delle citta` per
lettera alfabetica, sarebbe inutile. Una possibile scrematura
potrebbe essere invece una suddivisione delle citta` per nazione (non
fosse altro per un fatto di ordinamento dei dati in maniera piu`
coerente). Infine, si, sono interessato al database :)
Henryx

eventuali indici sul campo. 
Per quanto riguarda PostgreSQL (almeno la 8.x):
"The optimizer can also use a B-tree index for queries involving the
pattern matching operators LIKE, ILIKE, ~, and ~*, if the pattern is
anchored to the beginning of the string, e.g., col LIKE 'foo%' or col
~ '^foo', but not col LIKE '%bar'"
Saluti  Manlio Perillo

 Henryx
-- 
chmod a-rwx The\ Untouchables.avi

e cmq se usi il db guarda che meraviglia:
http://www.zipcodeworld.com/samples/distance.php.html
