Many schemas are simple, with one text field that is searched against, and one or two other fields that are displayed, or used to identify the record, e.g. id, title, body. Other schemas have many more fields, sometimes with dozens of fields that need searching individually or together. We suggest that you carefully review such a schema to verify that it is needed. If it is, and you anticipate searching one to many fields with text queries the following can help. Using the virtual field function of Texis, create an index covering all the fields you are going to be doing text searches against, along with any fixed fields as a compound index, in addition to the indices you would normally create. If the search terms are concatenated together first, and searched against that index you stand a good chance of reducing the possible result set as much as possible, as early as possible, which as already discussed is a good idea.

For example, in a resume application you may with a SQL statement that looks something like:

<$null=''>
<SQL "SELECT FirstName, LastName from resumes
	WHERE FirstName like $qfname
	  AND LastName like $qlname
	  AND Skills like $qskills
	  AND Education like $qedu
	  AND Title like $qtitle
	  AND Access like $qaccess
	  AND Resume like $qresume;">

There are a few possible ways that this might be improved, depending on how the data is stored. The first thing to look at is the Access field. Using a like on the Access field can be a useful idea if there may be a few different access codes attached to the record, and like can pull the appropriate one out efficiently. Since we are going to be building virtual fields that include the access code it makes sense to make the codes unique. One way to achieve that is to add a unique prefix to the front of the code, such as "ac_". This will prevent any confusion between access codes and other words in the record. This needs to be taken into account when writing the index expression.

If the various fields are simply extracted from the Resume field, then you probably want to search a virtual field of Resume and Access first, e.g.

<sum "%s " "" $qfname $qlname $qskills $qedu $qtitle $qaccess $qresume>
<$qfull=$ret>
<$null=''>
<SQL "SELECT FirstName, LastName from resumes
 	WHERE Resume\Access like $qfull
	  AND FirstName like $qfname
	  AND LastName like $qlname
	  AND Skills like $qskills
	  AND Education like $qedu
	  AND Title like $qtitle
	  AND Access like $qaccess
	  AND Resume like $qresume;">

If the fields are not directly extracted from the Resume field, for example if they are processed to extract or normalize the keywords, then you may want to do something a little different, such as:

<sum "%s " "" $qfname $qlname $qskills $qedu $qtitle $qaccess $qresume>
<$qfull=$ret>
<$null=''>
<SQL "SELECT FirstName, LastName from resumes
 	WHERE FirstName\LastName\Skills\Education\Title\Resume\Access like $qfull
	  AND FirstName like $qfname
	  AND LastName like $qlname
	  AND Skills like $qskills
	  AND Education like $qedu
	  AND Title like $qtitle
	  AND Access like $qaccess
	  AND Resume like $qresume;">