> Just wondering if you can answer me this on Indexing;
> If you have several Indexes setup only on individual fields,
> do these effect queries which use several fields which have
> different indexes, or do we need to build an index with all
> of the effected fields?
>
> e.g.
> Index on CompanyName
> Index on PersonName
>
> select PersonName, CompanyName
> where PersonName like '%smith%' and CompanyName like '%IBM%'
>
> Would this use the 2 indexes, or would I need an index on
> PersonName and CompanyName?
Individual indexes will work fine for individual filtering conditions. If
you always searched on both fields together, you'd probably be better served
with a single index containing both columns. If you ever used either column
individually as a search filter, you'd probably be better off with
individual indexes.
However, in your code sample, you'd probably end up with a table scan, since
indexes aren't generally used for in-string search filters. If your filters
looked like this:
WHERE PersonName LIKE 'smith%'
AND CompanyName LIKE 'IBM%'
you'd be able to use indexes on those fields.
Since you're a SQL Server guy, if you're interested in learning more about
how SQL Server uses indexes, the best teacher is SHOWPLAN.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
