Dave Cramer wrote: > Interesting it works now, and the good news is it is *WAY* faster, this > might be able to speed up marc's doc search by orders of magnitude > > this is searching 100536 rows > > select * from url where fn_strrev(url) like fn_strrev('%beta12.html'); > 1.57ms > > > explain select * from url where url like '%beta12.html'; > 3310.38 ms
The nice thing about this is that you can create your query thusly: SELECT * from table WHERE column like 'string' AND fn_strrev(column) LIKE fn_strrev('string') and, if you have both a standard index on column and a functional index on fn_strrev(column), the query will be fast (well, as fast as the pattern in question allows) as long as 'string' is anchored on either end. I've implemented the 'locate' utility in Perl using a PG backend instead of the standard locate database. I internally convert globs given as arguments into LIKE strings, and with a functional index like that the searches are now blazingly fast -- faster than the original 'locate' utility. It has the added advantage that you can specify a file type to further narrow the search (thus 'locate --type file "core"' will find all regular files named 'core' in the database). I'll be happy to share my code with anyone who's interested. -- Kevin Brown [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly