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.