MySQL Performance Tuning

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Syntax

  • Don't use DISTINCT and GROUP BY in the same SELECT.

  • Don't paginate via OFFSET, "remember where you left off".

  • WHERE (a,b) = (22,33) does not optimize at all.

  • Explicitly say ALL or DISTINCT after UNION -- it reminds you pick between the faster ALL or the slower DISTINCT.

  • Don't use SELECT *, especially if you have TEXT or BLOB columns that you don't need. There is overhead in tmp tables and transmission.

  • It is faster when the GROUP BY and ORDER BY can have exactly the same list.

  • Don't use FORCE INDEX; it may help today, but will probably hurt tomorrow.

Remarks

See also discussions about ORDER BY, LIKE, REGEXP, etc. Note: this needs editing with links and more Topics.

Cookbook on building optimal indexes.



Got any MySQL Question?