- avoid subqueries if possible (not sure if this still holds true with the latest versions of mysql),
- avoid using offset and limit for pagination, it's a killer on big datasets,
- on huge datasets sometimes breaking a complex query into 2-3 smaller ones can speed up things a lot, so always use explain and common sense when writing queries,
- even with billion rows if query is really slow, it's probably missing a proper index in like 9 of 10 times
I always put an index on nearly every field that could potentially be used to do a SELECT or an ORDER BY on. I'm not sure I've ever run into a serious problem doing this. Usually, the opposite is true - not having an index can cause serious problems.
In a way, I look at indexes like I used to look at these "turbo" buttons (1) on older PCs: no reason not to press it.
Indexes mostly have an impact on write performance and space used on disk, so if both are not a concern for your use case, creating lots of indexes is fine.
- avoid using offset and limit for pagination, it's a killer on big datasets,
- on huge datasets sometimes breaking a complex query into 2-3 smaller ones can speed up things a lot, so always use explain and common sense when writing queries,
- even with billion rows if query is really slow, it's probably missing a proper index in like 9 of 10 times