How indexing can kill your app or make it snappy.
it is a common misunderstand that indexes make things faster. This is a truth with some major reservations.
Indexes are a necessity for speedy operation but is is also an Achilles heel.
Indexes are files and if you look in the folder containing you app you can see that they sometimes are bigger than the corresponding DBM file.
In these files the index btree is stored and this need to be maintained when you add, modify and delete records. So if you have a lot of indexes this maintenance job takes a long time.
If you have a subform and delete all the rows in that subform too this job is multiplied by the number of rows in the subform.
Indexes will only be used for the first part of a search so if you have a relationship with 3 field constraints the index will only be useful on the first filter.
SalesData here being indexed will give a good precision but there is no point indexing customername and CustomerActive as they will be sorted in the result set. This is why its important to have the best filter first (the one that gives the least amount of hits).
Imagine if you flipped it and put customerActive first (a lot of people do).
Then you will get a hit on all active customers on a Yes/No field that is very bad for indexing as it will give long branches rather than many short.
We had a customer that did this on a table with over 2 million records.
Their filter was:
So what they wanted was all active customers of "main" type for the last 14 days.
98% of their 2 million customers where of type Normal so the index was wonkey and the result set returned by it was over 1.95 Million!
So that was it for the index.
Then 60% of their customers where still active so we now had 1.18 million still.
But as the records went back over 10 years, only approximatley 9000 was from the last 14 days.
This report took 6 hours to run on a standalone computer.
We swapped the filter over:
The report could be run in the network and it took less than 10 seconds....
This is just an example on how indexes and proper used of them can make your application and if not in practice - break it.
1. Use indexes only when you know why. Don't put them on because it could be useful. Less is more.
2. The better spread an field give, the better is the index and hence faster.
4. Realise that an index in itself is a data file and the more you have and the more unbalanced they are (indexes on yes/no etc) the slower they are to be maintained.
5. An index file will keep on growing until it is reorganized. If you use a sorting table for instance where you pump data over,manipulate and then delete the indexes will keep on growing util you reorganize that table. SO you can have an empty table with an enormous index file(s)