I use a linux/mandrake 7.2 on PIII 350
when doing
****************1 - create an initialisation file
i=0;
loadfile="/usr/local/pgsql/param/loadfile"
rm -fr $loadfile ;
#creating a file with 1500 records
while [ $i -lt 1500 ] ; do
i=`expr $i + 1`;
mod=`expr $i % 10`;
#creating a field amount with different numbers
mont=`expr $mod \* 18 + $i `;
echo $i"|"nom$i"|"prenom$i"|"$mont>>$loadfile;
if [ $mod -eq 0 ] ; then
echo " $i lignes created " ;
fi
done
echo "Load file done " ;
************* 2 - creating and populating database
psql essai <<++
create table names (
id integer,
nom char(40) ,
prenom char(20),
montant decimal
);
copy names from '/usr/local/pgsql/param/loadfile' delimiters '|';
create unique index id_names on names(id);
create index nom_names on names(nom); ++
************** 3 - executing a select
psql essai <<++
select sum (montant) from names
where nom in (select nom from names where nom like '%1%' );
++
*************** 4 - checking results
command : time sql.sh
sum
---------------
787494.000000
(1 row)
0.01user 0.01system 0:12.08elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (421major+110minor)pagefaults 0swaps
it took 12 seconds (I did the same with an informix SE database and the result is
(sum)
787494.00
1 row(s) retrieved.
real 0m0.62s
user 0m0.03s
sys 0m0.03s
****************** 5 - Other tests
if I change the like clause and execute :
psql essai <<++
select sum (montant) from names
where nom in (select nom from names where nom like '%12%' );
++
the result is
sum
---------------
157132.000000
(1 row)
0.02user 0.01system 0:00.56elapsed 5%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (422major+109minor)pagefaults 0swaps
with informix it's nearly the same
In both cases I also did the same whith dropping the indexes
and the results are quite the same.
can someone explain me why there's a so huge difference
between LIKE '%1%' and LIKE '%12%' ?
thanks for all
denis
