Обсуждение: search on tables
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.
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.