When using likep on multiple fields in a query, is it possible to weight certain fields as being more important than other fields. For example, if I were to use the following query:
Select * form sometable where fieldA likep $valueA or fieldB likep $valueB
Is there a way for me tell Texis that I want to treat the search on fieldA as being more important than the search in fieldB (For ranking).
Ive looked through the apicp options as well as several other help pages related to queries and I dont see anything on this.
Not directly, but if you use a virtual field and merge the queries:
create metamorph inverted index myindex on sometable(fieldA\fieldB);
select * from sometable where fieldA\fieldB likep $query;
(note the backslash between the fields) then hits in fieldA are generally weighted more than fieldB, due to likepleadbias: fieldA occurs first. This is also a faster search, because there's only one index involved, and you get accurate $rows.min/$rows.max/$indexcount values.
Though, in the example above the value you search for in both fields would have to be the same. Meaning, I wouldn't be able to search for one value in fieldA and a different value in fieldB. Is that correct?
Correct. However, depending on your data, if you know that $valueA can't ever be in fieldB and vice versa (eg. fieldA is Color and fieldB is Manufacturer, and $valueA and $valueB are from select boxes), and $valueA and $valueB are single terms, then you can still take advantage of the speed of a single virtual field index by making the query be:
$valueA $valueB @0
against fieldA\fieldB. Because of ranking, a hit on both values would be ranked highest; a single hit in fieldA is ranked lower, then a single hit in fieldB is even lower (likepleadbias).
Again, it depends on your data, but it's worth considering for speed if your data and queries allow it.
The virtual field as described aboved seems to be very fast when doing queries against our database. Such as:
select * from sometable where fieldA\fieldB likep $query;
I'm running into a problem when I add a filter for a date field:
select * from sometable where fieldA\fieldB likep $query AND dateField < 'today' ;
The query still works but is very slow. I added both an index and an Inverted Index for the dateField but it is much slower. It is running against several hundred thousand records. Do you have any ideas on how I can improve the performance?
create metamorph inverted index myindex on sometable(fieldA\fieldB, dateField);
This didn't seem to speed up the query. Do I need to write the Select statement differently or do I need to set a server setting? I left the Select statement the same as before:
select * from sometable where fieldA\fieldB likep $query AND dateField < 'today' ;
Since you are using Vortex it might help to cast the string 'today' to a date first, and pass that in as a parameter, although recent versions should handle that automatically. For example:
<$today=(convert( 'today' , 'date' ))>
<SQL ROW "select * from sometable where fieldA\fieldB likep $query AND dateField < $today">
It appears when I use the date check in the query, it doesn't seem to use my 'likeprows' setting ($rows.max is upwards of 30,000 - 40,000 recs). If I remove the date check it then seems to obey the 'likeprows' setting.
The SQL function I'm using needs to be able to specify a page of data. For example:
<sql skip=$skp max=20 $select ></sql>
I tried setting 'likepmode' and that didn't seem to work. Is there a way to force the max recs in in this situation?
In general with an AND clause then all the records that match the LIKEP need to be returned, to ensure that you end up with at least likeprows that match the other portions of the query, although it should only return the first likeprows.
