Query on Compound Index

[Texis User]

saw this article on http://www.thunderstone.com/texis/site/ ... pound.html.

So for a query
<SQL ROW "SELECT Url FROM html
WHERE Title\Description\Keywords\Meta\Body LIKE $query
AND (Visited BETWEEN $first AND $last
AND Depth BETWEEN $low and $high) " $orderby>

We create index

CREATE METAMORPH INVERTED INDEX xhtmlbodvd ON HTML(Title\Description\Keywords\Meta\Body, Visited, Depth);

I have a scenario wherein user could use visited and/or depth as his search criteria.
Would above index be used if visited or depth are not in where clause.

Thanks

[Kai]

Yes, that index would be used even if a Visited and/or Depth clause is not present in the query, as long as the first thing in the WHERE clause is Title\Description\Keywords\Meta\Body LIKE $query. Use of the compound fields (the 2nd and later comma-separated fields) in a Metamorph compound index is optional, and their order of use does not matter (after the LIKE).

[Texis User]

I have created this index

create metamorph inverted index xpersonid on person(id, business_name, sub_business_name , function_name , location);

And I am trying to run this query:

select *
from person where id likep '500*' and active_flag=0
and function_name = 'Function Not Available' and business_name = 'Corporate'
and sub_business_name = 'Initiatives Group' and location like 'India Maharashtra Mumbai*'
order by initcap(last_name) asc, initcap(first_name) asc


It gives me these errors
Vortex (1963) ABEND: signal 11 (SIGSEGV)

[John]

What type is id? The first field in a metamorph index should always be a text field (single or compound), either varchar or blob.

Generally the other fields are fixed fields, such as dates and numbers.

[Texis User]

yes id is varchar(50).

Another q when i create a index

create metamorph inverted index xpersonid on person(id, business_name, sub_business_name , function_name , location);


I can use like/likep operator with business_name, sub_business_name , function_name , location rt?

[John]

No, you should create a separate index for other fields you want to do a like on, e.g. location in your query above. For example active_flag might be a good choice for an additional field.

[Texis User]

I have now created this

create metamorph index meta_profile_index_location
on person_profile (location) ;

create metamorph inverted index xpersonohr_id on person_profile
(id, active_flag, business_name, sub_business_name , function_name , first_name, last_name);

And I am using this query

from person where id likep '500*' and active_flag=0
and function_name = 'Function Not Available' and business_name = 'Corporate'
and sub_business_name = 'Initiatives Group' and location like 'India Maharashtra Mumbai*'
order by initcap(last_name) asc, initcap(first_name) asc

Still getting
00 2007-03-01 09:58:46 /webinator/SCProfileSearch:753: Vortex (7008) ABEND: signal 11 (SIGSEGV)
000 2007-03-01 09:59:58 /webinator/SCSearchProfile:490: Vortex (7055) ABEND: signal 11 (SIGSEGV)
000 2007-03-01 10:00:05 Vortex (7088) ABEND: signal 11 (SIGSEGV)
000 2007-03-01 10:00:09 Vortex (7096) ABEND: signal 11 (SIGSEGV)

[Texis User]

Continuing the previous thread

When I run

select * from person_profile where id likep '50088*' and active_flag=0 order by initcap(last_name), initcap(first_name) asc

Now I see no "Vortex (7088) ABEND: signal 11 (SIGSEGV"
but see this error

200 2007-03-01 11:55:03 /webinator/SCSearchProfile:127: Can't handle ORDER BY with compound index


Would like to understand this,Pls help.

[John]

The 200 message is an informational message, not an error, indicating that the order by could not be fully resolved with the compound index, and so it will be done without the index.

[Texis User]

What abt this -

I have now created this

create metamorph index meta_profile_index_location
on person_profile (location) ;

create metamorph inverted index xpersonohr_id on person_profile
(id, active_flag, business_name, sub_business_name , function_name , first_name, last_name);

And I am using this query

from person where id likep '500*' and active_flag=0
and function_name = 'Function Not Available' and business_name = 'Corporate'
and sub_business_name = 'Initiatives Group' and location like 'India Maharashtra Mumbai*'
order by initcap(last_name) asc, initcap(first_name) asc

Still getting
00 2007-03-01 09:58:46 /webinator/SCProfileSearch:753: Vortex (7008) ABEND: signal 11 (SIGSEGV)
000 2007-03-01 09:59:58 /webinator/SCSearchProfile:490: Vortex (7055) ABEND: signal 11 (SIGSEGV)
000 2007-03-01 10:00:05 Vortex (7088) ABEND: signal 11 (SIGSEGV)
000 2007-03-01 10:00:09 Vortex (7096) ABEND: signal 11 (SIGSEGV)


Would like to understand this,Pls help.