slow performance with queries

phoebe · Post by **phoebe** » Wed Jan 07, 2004 12:59 pm

Hi,

I am trying to run some queries with multiple categories, first I tried:
select Url,Catno from html where Title\Description\Keywords\Meta\Body likep 'library' and Catno liker '2 7';

which gives me the correct result, but is very slow with large result sets (> 50,000 out of 1.3 mil pages).

A previous post suggested making a compound index with both fields. I tried:
CREATE metamorph inverted index xhtmlbodv on html (Title\Description\Keywords\Meta\Body,Catno);
100 Variable size warning

and when I run the same query, I get no results.

I have set "bubble=0", "likerrows" and "likeprows".

Is there a way to speed up this query?

Then I tried the following:
select Url,Catno,count(*) from html where Title\Description\Keywords\Meta\Body likep 'cancer' and Catno liker '2 7' group by Depth;

It becomes unusable. It takes almost a minute. It spends most of it's time in the function groupbysetup2. (There is an index on Depth)

Is there a way to speed up this operation?

P.

phoebe · Post by **phoebe** » Wed Jan 28, 2004 2:39 pm

Thanks, like3 seems a lot faster!

phoebe · Post by **phoebe** » Wed Jan 28, 2004 5:50 pm

I only care if it is in all the categories listed ( no intersections)
I tried it against likep and it seems like that likep is a lot faster than both liker and like3.
Is it because of the parameters likeprows=100, likepallmatch=1, likepinf*=500000, etc. Using likep also has the side effect of changing the ranking from 90-100% to 30-40%.

Is there anything I can set to improve like3 performance?

phoebe · Post by **phoebe** » Thu Jan 29, 2004 12:41 pm

I have a metamorph index on categories and some of the categories have over a million members.
LIKE is very slow. It times out the 2 minute limit regularly.
LIKE3 at least comes back after 70 seconds or so in some cases. LIKEP comes back in about 5 seconds, but the results are completely different.
Is there a setting to speed up LIKE?
I have already increased cache size and RAM pages.

Post by **mark** » Thu Jan 29, 2004 2:48 pm

And is the metamorph index up to date?

phoebe · Post by **phoebe** » Thu Jan 29, 2004 11:00 pm

The version is:
Enterprise Webinator Version 4.03.1054758060 of Jun 4, 2003 (i686-unknown-linux2.4.9-64-32)

I am not using any particular setting for building the index, just the default. The indices are dropped and recreated only after big updates about once a week. They will be slightly out of date occasionally.

For searches, I am using :
set ignorenewlist=1
to deal with little time between each index updates.

phoebe · Post by **phoebe** » Fri Jan 30, 2004 12:37 pm

There is a metamorph index on Catno.

phoebe · Post by **phoebe** » Fri Jan 30, 2004 6:12 pm

Actually, the categories are of 2 characters, \alpha\digit so that they are not confused with noise words.
I did not changed any of the other webinator settings.

All the html pages have at least one category and in one version I use both '+' and '-' in the search. It doesn't seem to make a difference in the performance with '+' added.

The index expression is only the simple:
create metamorph index xhtmlcat on html(Catno)

When it doesn't time out, it just returns the query with no mesg.

Post by **mark** » Mon Feb 02, 2004 2:05 pm

I'm confused by that last statement. Are you saying that you did a query that should have had answers but didn't?

View the source of the results page to see if there are any error or warning messages within html comments.

phoebe · Post by **phoebe** » Thu Feb 05, 2004 6:47 pm

I get a blank page.
page source is also blank.