Here are few things I learned:

• Learn the EXPLAIN output from explain and Explain Demystified
• See how Optimizer made decision
• Use index, but don't abuse index, because index takes space
• String column could use index prefix to save space (pick length wisely)
• OR cannot leverage index (maybe you want UNION)
• MySQL not using your index: Index Hints
  • FORCE INDEX tell MySQL to use particular index
• The index is the cause of slow
  • IGNORE INDEX tell MySQL NOT to use particular index
• Normalize or denormalize
• Do Multiple-row INSERT instead of INSERT
• Large Data Manipulation
  • SELECT INTO OUTFILE + LOAD DATA INFILE
  • Amazon RDS, Aurora: SELECT INTO OUTFILE S3 + LOAD DATA FROM S3
• Optimize for InnoDB