Indexing

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 a
1) There's a limit of 32 indexes per table
2) Indexes take up space
3) Indexes can make inserts and updates slower

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)



"Al Camp" wrote in message
...
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

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

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


"Douglas J. Steele" wrote in
message ...
Among the reasons why you don't want to index every field a
1) There's a limit of 32 indexes per table
2) Indexes take up space
3) Indexes can make inserts and updates slower

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)



"Al Camp" wrote in message
...
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