Facet Queries

[gazim]

I am trying to display five facets with my search. When the search results are displayed, I assume Texis executes a total of 4 queries (3 facet queries and 1 master query for the search). Is there a recommended way to optimize these searches in an attempt to minimize the number of query executions? What's the most optimized way to execute these facet queries?

Thanks in advance.


Columns in Table:
ID
AUTHOR
LOADDATE
DOCTYPE
TEXT

Facet Fields:
AUTHOR, LOADDATE, DOCTYPE





sample code:
<!-- 3 facet queries -->
<p><b><u>Author</u></b><br />
<sql max=15 "select AUTHOR,count(*) totalcount from mytable where DOCTEXT like 'insurance' group by AUTHOR order by 2 desc">

$AUTHOR ($totalcount )<br />

</sql>

<p><b><u>Load Date</u></b><br />
<sql max=15 "select LOADDATE,count(*) totalcount from mytable where DOCTEXT like 'insurance' group by LOADDATE order by 2 desc">

$LOADDATE ($totalcount)<br />

</sql>

<p><b><u> DOCUMENT_TYPE</u></b><br />
<sql max=15 "select DOCUMENT_TYPE,count(*) totalcount from mytable where DOCTEXT like 'insurance' group by DOCUMENT_TYPE order by 2 desc">

$DOCUMENT_TYPE ($totalcount)<br />

</sql>

<!-- main query -->

<$strsql="select ID,AUTHOR,LOADDATE from tblflorida where DOCTEXT like 'insurance'">

<sql $strsql>
DOCID: $ID <br />
AUTHOR: $AUTHOR <br />
LOADDATE: $LOADDATE <br />
DOCTYPE: $DOCTYPE <br />
<p>
</sql>

[John]

That depends on precisely the type of index you have. The two approaches would be either selecting the fields for the facet along with the result fields, and using xtrees to build the facets in vortex. That works best for strings.

Another approach would be to use:
<sql "set options='indexcache'"></sql> before the queries, which will allow it to cache much of the work. That is especially helpful if the facets are compound fields in the metamorph index.