3 ms·
- 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 kil
by ivanhoe 7y ago
- 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
- akie 7y agoI 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. (1) https://en.wikipedia.org/wiki/Turbo_button https://en.wikipedia.org/wiki/Turbo_button
- zambal 7y agoIndexes 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.
- ivanhoe 7y agoOh, yes, turbo button, the only thing more useless than caps lock :) Usually compound indexes are the best idea, but you need first to see what queries are running against DB to know what to add.