Since most of us are not DBAs, I figured we could share some tips and tricks relevant to the databases we are using.
You need to index your columns which may appear inside a where clause. However, having indexes that are never used is also bad. An example of this is in a wide historical table, with update times greater then 1 second. Most of the time, an auto incrementing index is created. This index can become very large, degrading table performance. For historical tables, consider using the timestamp as your primary key
InnoDB uses clustered indexes. These indexes read from left to right. If you index on say (timestamp, pointID), then an where clause that will use this index properly would look like this
where timestamp > [i]sometime[/i]
where timestamp > [i]sometime[/i] and pointID = [i]somepointID[/i]
where pointID = [i]somepointID[/i] and timestamp > [i]sometime[/i]
These 3 where clauses will use the indexes to find relevant rows, and not perform a full table scan
However, if you do this
where pointID = [i]somepointID[/i]
it will perform a full table scan because the index timestamp does not appear in the where clause
3. Learn to use EXPLAIN
EXPLAIN is extremely useful when writing queries. It will show you how the query will be executed, what indexes it will use, if it will use a range, full table scan, or indexes to find the rows, etc. EXPLAIN SELECT is your friend
4.Partition your large tables
Modified to respond to Colby. If a wide table has an update rate >= 1 second, then the primary key can be the timestamp. If you are using a tall table, then your primary key would need to be like (timestamp,pointID) to work.