Sidar,
1) nombre like 'LOPEZ CRUZ SIDAR%' is much more
selective than
2) nombre like 'lopez%sidar%'
The database needs to get all rows that match up to the
first wildcard.
In 1) that is most likely a very low number and your
index will help PostgreSQL to find those rows quickly.
In 2) that is potetially 1,000s of rows out of the
5,000,000 in your table. In a second step the end
result is selected from the records that match 'lopez%'
From a performance standpoint the worst you can do with
LIKE is to have the wildcard at the very beginning.
Then PostgreSQL needs to read all records.
For example
WHERE nombre LIKE '%lopez%';
However, this may get you a match for "Jennifer Lopez".
Many applications have a column first_name and another
for last_name.
Then you can have a more selective WHERE clause
WHERE last_name='Lopez'
AND first_name like 'J%'
Regards,
Nikolaus Dilger
On Wed, 26 Mar 2003, "Sidar Lopez Cruz" wrote:
why searching with like or ilike on tables with too
many records is too slow?
i have a table like this
create table sujetos (cedula varchar(20) not null
primary key, nombre varchar(255), id_tipo_documento
bigint);
create index idx_nombre on sujetos
(nombre);
and them, i insert 5,000,000 of records from
mssql
then, i do something like that, select * from
sujetos where nombre like 'LOPEZ CRUZ SIDAR%', and the
request from the server
is normal, but when i do this, select * from sujetos
where nombre like
'lopez%sidar%';
the server die.... searching this
string....
:-) Sidar Lopez Cruz- Cero Riesgo,
S.A.
