Optimizing MySQL performance

- Use of Index
- The EXPLAIN keyword - analyze the execution of a query.
- Avoid calculation on a field. e.g., use "rate < 40" instead of "rate / 2 < 20".
- MySQL uses leftmost prefixing, which means that a multi-field index A,B,C will also be used to search not only for a,b,c combinations, but also A,B as well as just A.
- The Query Optimizer, OPTIMIZE and ANALYZE.
  ANALYZE TABLE tablename 
  OPTIMIZE TABLE tablename 
- Use short index: index the first few characters of a field. e.g., ALTER TABLE employee ADD INDEX(surname(20),firstname(20));
- Load data in batch, instead of insert 1-by-1:
  $db->query("LOAD DATA INFILE 'datafile.txt' INTO TABLE employee (employee_number,firstname,surname,tel_no,salary) FIELDS TERMINATED BY '|'");
  LOAD DATA INFILE has defaults of:
  FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '' 

  INSERT LOW PRIORITY - insert only when no read.
  INSERT DELAYED - non-block insert, put insert requests on a queue.

- Fast delete: in MySQL 4.0 or later
  TRUNCATE TABLE classifieds; 
  runs faster than
  DELETE FROM classifieds;
  since "TRUNCATE" deletes all at once, but "DELETE" deletes one by one.

Reference:
[1] databasejournal.com: Optimizing MySQL: Queries and Indexes