I'm not completely sure when would I start indexing my tables, when there are some criterias that I should consider:
- The tables are not that big, I think (1000 - 10,000 rows).
- The searches are made from the CMS admin pages - So they include more types of filtering/sorting options/combinations than the frontend.
- I'm wondering if there is such thing as over-indexing where I'll add too much indexes that the searches might be quicker, but it will impact dramatically on the performances of inserting/updating data to the table, or that the DB engine will choose the wrong index for a query.
- I'm adding
Fulltextindex when it come to textual searches.
- I'm also adding asc/desc indexes for
DATETIME/INT/BIGINTfor what I believe will give better performances when it comes to sorting/filtering based on columns.
I'm just not sure what will be the optimal point in which adding an index will improve the performances of my App, when I take into consideration the above parameters, and my tendency to be an "Index Nazi" (Pardon my French) - I'm finding myself adding more and more indexes for every possible scenario I can think of.
I do hope that this question is not too broad, and I didn't include an example because it's more general behaviour of good and bad practices when it come to indexing.