Yep I realise this....just trying to make the point as to when and why you'd
use indexes....I also think you should limit the amount of fields use in
your index. Don't just put them in their for the hell of it. If you
include fileds in your where clause that are in an index then this will
improve performance. Obviously in real life it works the other way
round.....so you vuild your indexes around your where clauses.
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 488 9131
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 25 July 2001 13:36
To: CF-Talk
Subject: Re: SQL Query
No ... having too many indexes can actually lead to decreased selection
speed. This is because SQL generates an execution plan in which it examines
the available indexes and picks the one(s) it thinks is best.
The way to maximize selection speed for a single query is to create and test
individual indexes and benchmark them. Don't forget to try various composite
indexes ... wide index containing more than a single column which typically
mach your Where and Order By clauses.
Note, that the available indexes are used for all queries against the table.
So optimization for a specific query could decrease speed of other queries.
Also, SQL provides the Index Tuning Wizard which allows you to select and
create an optimal set of indexes.
Cheers,
Bill
In a message dated 7/25/01 8:14:24 AM Eastern Daylight Time,
[EMAIL PROTECTED] writes:
> > Index the table to hell and back
>
> >>Indexing too many fields, or the wrong combinations, can actualy hurt
> >>performance.
> >>It's best to put a few benchmarks in place, and add a few indices at a
> time.
>
> True.....indexes improve speed of selects but slow down inserts,updates
and
> deletes as the whole index needs to be rebuilt every time data is changed.
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
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
