Unable to speed up the search for document extension

[Priti Barapatre]

Hi,
I am trying to display the results based on the document extension( .doc, .ppt, etc) selected by user.
Currently i am using
select * from table_name where Url matches "%.doc";
this query is much slow and keeps on feeding the search.
Other way, I tried to do same by an index over the "Url" column.
select * from table_name where Url likep ".doc";
But still dont work.
Could you please provide some other way to implement the same.

[John]

If you create a metamorph index with an index expression that includes '>>\.=\alnum{1,4}' that will include extensions 1 to 4 characters long, and a LIKE search on URL should be faster.

If you know you are going to be searching on extension a lot it may make more sense to add a field for extension, and index and search that.

[mark]

Ideally you should have a separate column for the extension so you can do a direct lookup.

No index will help the matches unless it were a prefix match.

For your metamorph index on Url you'd need to add an index expression to match just the extensions. Something like "\.[^./]{1,5}>>=" and delete the default expression if you don't need to otherwise search the url field for words. Use like instead of likep for that search.