-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stefan Sturm Sent: Tuesday, July 22, 2008 11:31 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Optimizing a like-cause
Hello, I'm developing a autocomplete Feature using php and PostgreSQL 8.3. To fill the autocomplete box I use the following SQL Statement: select * from _table_ where upper( _field_ ) like '%STRING%'; This SQL Statement takes 900 ms on a Table with 300.000 entries. What can I do to speed up the Statement? What Index can I set? >> If you are searching for words, you could use tsearch2. If you are searching for arbitrary fragments, an idea like this might prove helpful: http://kaiv.wordpress.com/2007/12/11/postgresql-substring-search/ What you are asking for is very difficult, because an ordinary index won't help (you have a wildcard on the front) and an index on the reversed word won't help either (you have a wildcard on the back). So the standard sort of techniques used to solve it are not perfectly on target. << -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
