Diary

Tuning MySQL query performance

Tuning MySQL query performance these days, jot down my retrospect and learning.

Soft knowledge

  1. Calm down when facing problem
    • It is common sense, but really difficult to do.
  2. Always jot down “stupid” solutions
    • I have reject a solution because of a wrong assumption
      • You don’t know whether a solution is stupid, unless you get the full picture
      • May be there is no stupid solution, only appropriate solution
    • Eliminate potential solution too early can slowing down the development

Hard knowledge

  • How to read MySQL exercution plan
    • Nice visualizing tool: MySQL workbench
  • Join table on the fields with index
  • We can use USE INDEX() to specific which index to use
    • We don’t need to re-write the query
  • MySQL store long text field as a file pointer
    • So reading long text field involve disk IO, can be very slow
    • Don’t filter and join tables on long text field
  • Sometimes getting long text field is fast
    • Because kernel level may have disk cache
  • MySQL cannot use index when joining tables on long text field
    • Use CAST() to convert to fixed length field
      • e.g. CAST(long_long_long_text AS char(50))
  • Joining long text field in server can be faster than joining in DB
    • overhead in network and computation cost is smaller than disk IO cost in DB

Resources