Having problems with search over "publications"

henryj0 · Post by **henryj0** » Tue Jun 10, 2003 6:42 pm

I have a query over an employee database that is searching where Title\Meta\Body over HTML table. The query works for fine in all cases except where $query is set to "publications" or "publ*". The query in question is "SELECT count(*) FROM html where Title\Meta\Body LIKE $query and Meta LIKE $query". If I remove the 'and' clause I receive the entire database count with 'and' clause I receive count of 0 (I am expecting 32). If I run this query from a unix command line I receive the expected 32. I also have similar results while experimenting with the where clause: Title\Meta, etc. Within the script I receive 0 count, but from the command line I receive values. The 'and' clause is used to remove references to employee supervisors with the search name. Why am I receiving a 0 count when I use the and clause? Hank.

Post by **John** » Tue Jun 10, 2003 7:51 pm

You might check for putmsg output in the HTML or vortex.log. Differences between script and command line generally indicate query protection kicking in, for example a missing index which would cause a linear scan. You should have an index on both Title\Meta\Body and Meta. I would note that if Meta LIKE $query is true, then Title\Meta\Body should be true as well, so you are asking it to do extraneous work.

henryj0 · Post by **henryj0** » Wed Jun 11, 2003 12:09 pm

John-
I sent your response to our network group who maintain the scripts which create the search table (html) and create indexes over the table. This was their response: "I am not performing any 'special' indexes, I am simply performing a complete index of the entire DB, so I'n not sure what they are referring to by 'indexing both'". Would a complete index resolve your suggestion of indexing for Table\Meta\Body and Meta?

Regarding your comment on validity of Meta and Title\Meta\Body query being the same: the purpose of the meta clause is to exclude elements that exist in the body. If I exclude the Body from Table\Meta\Body where clause I get 0 results. Hank.

Post by **John** » Wed Jun 11, 2003 12:50 pm

It sounds as if you should have created a metamorph inverted index on html(Meta), but have not. That is not an index that is built by Webinator by default, but could be added.

The fields you index should match the fields you will search.

henryj0 · Post by **henryj0** » Wed Jun 11, 2003 2:20 pm

Is there a way to create and inverted index through the gw utility? Hank.

Post by **mark** » Wed Jun 11, 2003 2:51 pm

gw only creates the default Webinator indices. To create anything custom you need to give a SQL statement to "texis" or write a small vortex small vortex script to execute the desired SQL.

See the manual for complete syntax to "create metamorph inverted index". There are also various examples throughout these message boards.

henryj0 · Post by **henryj0** » Thu Jun 12, 2003 12:27 pm

From a Unix command line I can run variations of a query and receive valid data. When using a script over Webinator my query only works if I have combine columns in my "where clause".

This works in Webinator and command line...
SELECT * FROM html WHERE Title\Meta\Body like 'dennis'

This will not work through Webinator, but does from the command line...
SELECT * FROM html WHERE Title\Meta LIKE 'dennis'

How can I get, or why can I not get, the second option to work in Webinator. Thanks, Hank.

Post by **mark** » Thu Jun 12, 2003 1:13 pm

The search script has query protection in place by default to prevent users from slamming your server by doing unindexed searches. Create the appropriate indices for the searches you want to do.