Optimizing SQL

Use SQL for the things it's good at, and do other things in your application. Use the SQL server to:

• Find rows based on WHERE clause.
• JOIN tables
• GROUP BY
• ORDER BY
• DISTINCT

Don't use an SQL server:

• To validate data (like date)
• As a calculator

Tips

• Use keys wisely.
• Keys are good for searches, but bad for inserts / updates of key columns.
• Keep by data in the 3rd normal database form, but don't be afraid of duplicating information or creating summary tables if you need more speed.
• Instead of doing a lot of GROUP BYs on a big table, create summary tables of the big table and query this instead.
• UPDATE table set count=count+1 where key_column=constant is very fast!
• For log tables, it's probably better to generate summary tables from them once in a while than try to keep the summary tables live.
• Take advantage of default values on INSERT.