What kind of index to use?

Faiz · Post by **Faiz** » Mon Nov 05, 2001 4:20 pm

I want to be able to search for either Word documents or PDF documents or HTML/TEXT documents, by matching their file extensions (.doc, .pdf, etc). Do I have to create a compound index on the field Url for better performance or just create a separate index on Url?
Url is a varchar field. Creating a metamorph index on it gives a warning. Is it okay?

Thanx,

Faiz · Post by **Faiz** » Tue Nov 13, 2001 2:04 pm

Thanx. I also have a couple of addexp statements. Is the following expression correct?
<sql "set delexp=0"></sql>
<sql "set addexp='\alnum{1,30}'"></sql>
<sql "set addexp='\.=\alnum{1,4}>>='"></sql>
<sql "set addexp='>>\alnum=[\alnum\+\_\x24\x27\x2E\xa0-\xff]{1,30}'"></sql>
<sql "create metamorph inverted index xdocbod on doc_category(Url\Title\Description\Keywords\Body,Prod,Catid,QPFlag,Docid,DocFlag)"></sql>

I could then run the query,
WHERE Url\Title\Description\Keywords\Body likep '$query + .pdf'
to search for PDF docs only AND
WHERE Url\Title\Description\Keywords\Body likep $query
to search all documents.

Please let me know whether the syntax is correct.

Regards,

Post by **mark** » Tue Nov 13, 2001 2:47 pm

There should not be a space between + and .pdf: +.pdf

Post by **mark** » Tue Nov 13, 2001 3:07 pm

Also, your usage of likep '$query + .pdf' is incorrect. It should be something like:
<sum "%s" $query " +.pdf"><$xquery=$ret>
<sql ... likep $xquery

Faiz · Post by **Faiz** » Tue Nov 13, 2001 4:25 pm

No records are returned when I try to find only PDF documents. Any idea where I might be going wrong? When I do a search on all documents, I get plenty of results though.

Post by **mark** » Tue Nov 13, 2001 4:49 pm

View the source of the results page and check for error/warning messages within html comments.

Also, did you drop and rebuild the metamorph index when you changed the index expressions? That's required.

Faiz · Post by **Faiz** » Tue Nov 13, 2001 6:23 pm

View source doesn't show any error or warning and I DO drop the index before creating it again. I am using the index expressions I mentioned in my previous posting to create the indexes. The sql query I use is this,
<sum "%s" $query " +.pdf"><$xquery=$ret>
<sql row "select id,Url,Title,Prod from doc_category
where Url\Title\Description\Keywords\Body likep $xquery
;">
and it returns no records. Is my index expression correct?

Post by **mark** » Tue Nov 13, 2001 9:55 pm

'\.=\alnum{1,4}>>=' would be good if you were searching just URL. For the virtual field you're using you would probably want '\.=\alnum{1,4}\F\n'

Faiz · Post by **Faiz** » Wed Nov 14, 2001 10:16 am

It works now. Thanx. Another quick question. If I want to be able to search documents posted within a time period, do I have to just add the DATE field in creating index(compound) and in the sql query, have it in where clause condition?

Regards,

Post by **mark** » Wed Nov 14, 2001 10:58 am

Yes, if you want to combine the likep search with a date restriction you should add the date field to the compound index. If you want to just search by date you should create a separate regular index on date.