2

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 Fulltext index when it come to textual searches.
  • I'm also adding asc/desc indexes for DATETIME/INT/BIGINT for 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.

3

Don't randomly add indexes. Look at your queries to decide which indexes are needed. See my cookbook.

InnoDB really needs a PRIMARY KEY. Keep in mind that a PK is, an index, is UNIQUE, and is clustered. So don't add any index(es) that start with the same column(s).

WHERE a=2 AND b=4 begs for a "composite" index: INDEX(a,b) or INDEX(b,a). Those are different than two separate indexes: INDEX(a), INDEX(b).

Don't use BIGINT unless you really expect to have huge numbers.

DESC is ignored on an INDEX declaration. However, that does not stop the optimizer from running through an index in reverse order to have the effect of 'descending'.

FULLTEXT is useful only if you use MATCH(...) AGAINST(...).

"Too many" indexes slows down inserts some, and slows down UPDATEs that change indexed columns.

Don't index "flags"; such indexes will 'never' be used.

2

For a table with 1000 rows I think it does not make sense to put any index. The complete table could fit into memory and that would be fast enough. But if you want to put indexes you should always take the business value into consideration. Take into account that each index generates an overhead to the table. Not sure where the limit is, but in any case check which are the most often used filters and use this as a first indicator for indexing

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.