> Unfortunately, I know very little about search features in
> ColdFusion. The best advice (not so good) that I could give
> him was to have a nightly batch job that would concatenate
> all fields from a single record and enter it into a single
> field of a new table. Then it could do a Verity index of this
> single column, allowing advanced searching across the record.
>
> He wondered whether there was a better way, and specifically
> whether there was a better way to do live queries without this
> sort of nightly batch job. His initial stab, an extremely
> complicated SQL statement, was prohibitively slow.
I'd probably recommend using Verity as well, but there's no reason to go to
all that work to use it. Just query the database to return the fields you
want indexed, and index those fields; don't bother with creating a new table
or any of that part.
<cfquery name="qIndexTable1" ...>
SELECT primarykey,
field1,
field2,
field3,
...
FROM Table1
</cfquery>
<cfindex action="refresh"
collection="mycollection"
query="primarykey"
title="field1"
body="field1,field2,field3,..."
...>
<cfquery name="qIndexTable2" ...>
...
</cfquery>
<cfindex action="update"
collection="mycollection" ...>
Note that this may take some time, so you probably want to do it on a
scheduled basis, and if you're using CF 4.5.x, you want to prevent anyone
from searching the collection until the indexing is complete. If it takes
long enough, you might consider using two collections, and indexing them on
an alternating schedule.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
