There are several SQL databases, among which MySQL is one; it has a number of unique features that are better than those of other databases due to its ease of implementation for enhancing efficiency and optimizing it. But due to this ease, many implementations land in an anti-pattern side, which results in the deterioration of performance.
This chapter provides different tips/best practices and techniques that help in overcoming these situations. We started with caching queries as shock absorbers for same query repetition, optimizing queries based on an execution plan using EXPLAIN, limiting the result set size with LIMIT, indexing search fields, the disadvantages of using SELECT */COUNT *, the recommended way of having an ID field along with other columns, optimization using prepared statements, splitting big DELETE/INSERT statements to avoid a deadlock state, and the...
