Indexing

  • Thread starter Thread starter Al Camp
  • Start date Start date
A

Al Camp

A friend has a very large "flat" database table of 367,000
records, with approx. 25 Text fields per record.

He said that his PrimaryKey AutoNumber field (IDNo) is
Indexed/NoDupes, and 12 other fields (about half of his table)
are Indexed/DupesOK.
He said he had Indexed almost half of the table fields to "speed
up the table".
(I mention again that this table is "flat"... no relations to
other tables)

Isn't there a price to pay in speed and/or size when "Indexing"
is overused??

If Indexing truly "speeds up" a table, why wouldn't we just Index
every field in our tables... even fields rarely used in
searches/sorts/filters/joins...??

Thanks
Al Camp
 
Among the reasons why you don't want to index every field are:
1) There's a limit of 32 indexes per table
2) Indexes take up space
3) Indexes can make inserts and updates slower
 
Thanks Douglas,
That's pretty much what I felt, especially the "size" factor.
In a LARGE table, adding indexes willy-nilly seems as though it
can do more harm than good.
Seems to me that a large "flat" table only needs a primary key
index, and that's it.
3) Indexes can make inserts and updates slower
Click to expand...

That's just what my friend is doing... Importing data, and
updating/adding/deleting records in a huge database, using that
imported data. Now I can make the case to him for removing those
unproductive indexes.

Thanks for the clarification,
Al Camp
 
Back
Top