Indexing Databases

When you generate a collection from a database, you actually build the index from a result set of a valid query you construct using the CFQUERY tag. The result set is indexed using the CFX_INDEX tag in which you specify a KEY, typically a unique value like the primary key, and the column in which you want to conduct searches, the BODY. The following extract shows only the CFQUERY and CFX_INDEX part of the process.

<CFQUERY NAME="Messages" DATASOURCE="CF 2.0 Examples">
   Select * from Messages 
   </CFQUERY>

This CFQUERY statement selects the entire table.

<CFX_INDEX 
   COLLECTION="DBINDEX" 
   ACTION="UPDATE" 
   TYPE="CUSTOM"  
   BODY="Body"  
   KEY="Message_ID"  
   TITLE="UserName" 
   QUERY="Messages">

This CFX_INDEX statement specifies the Body column as the core of the collection and names the KEY as the Message_ID column, the table's primary key. Note that the TITLE attribute names the UserName column from the Messages table. The TITLE attribute can be used to designate an output parameter. For example, a CFOUTPUT statement could be written as follows:

<CFOUTPUT>
   Message number #Message.Message_ID# was written by #Messages.TITLE#.
</CFOUTPUT>

Using concatenation to index multiple columns

To index more than one column in one collection you need to use the concatenation function of whatever DBMS you are using in your SELECT statement. For example, your SELECT statement might look like this:

SELECT FIRSTNAME+LASTNAME AS WHOLENAME

You would then generate a collection from WHOLENAME.

Advantages of indexing a database

When used to index a supported data source you can perform queries against that data source. The advantage of performing searches against a Verity collection to a CFQUERY is that the database is indexed in a form that provides fast access to data. Use this technique instead of CFQUERY if:

• You want to index textual data. Verity collections containing textual data can be searched much more efficiently with CFX_INDEX than searching a database with CFQUERY.
• You want to give your users access to data without interacting directly with the data source itself
• You want to improve the speed of queries
• You want your end users to run queries but not update a database table
• You do not want to expose your data source directly to the Web