Monday, March 26, 2007

IA: MySQL Optimization

Dr. Liddle recommended that we watch Jay Pipes' Google video presentation on MySQL Optimization. I compiled what I got out of the presentation with some of my own research and have summed up all of it into the following points:
  • Joins are more effective than sub-queries
  • Use a covering index; if MySQL can get everything for a query from the index records, it's much faster
  • Understand selectivity
    • For lower selective fields, tack them on to a multi-column index
  • As your DB grows, you will need to reevaluate your index strategy
  • Use the smallest data types possible, don't use BIGINT if you don't have to.
  • Index partial fields
    • if a field is of type VARCHAR(50 or 200), you can set an index on just the first 10 characters, this will allow for quicker parsing of the data
  • In a table with many nullable columns, split off the less used data and join the two on a one to one relationship
    • understand vertical and horizontal splitting
  • With InnoDB, the primary key index is appended to every field in the table
  • Don't use surrogate keys when naturally occurring primary keys already exist
  • Use stored procedures
  • Don't use current_date when you don't need the current time stamp, the query will not be stored. If you need to, have a script populate the fields with a date, that way the query will be stored at least for the day.
  • EXPLAIN is a very useful command
    • From best to worst, Type= system, const, eq_ref, ref, range, index, all
    • Extra=using temporary or filesort
  • Optimize where clauses by eliminating unnecessary parentheses

No comments: