Summary

Choosing the types for your fields is important if you want to get the best performance out of MySQL. Numeric types allow you to do calculations and are usually smaller than string types. Date types allow you to easily store date and time data.

Similarly, to use MySQL properly, you need to have an understanding of the operators it employs. Logical operators such as AND and OR, and comparison operators such as = and LIKE, assist in narrowing the results from your queries down to just what you need. Bit operators are a useful feature for binary mathematical data.

MySQL has a number of table types, which are suited to different situations. The MyISAM table type (the default) is ideal for systems where there are large numbers of queries relative to updates (such as websites). MERGE tables are amalgamations of identical MyISAM tables, allowing you to split large tables into a number of smaller ones, for ease of updating, and giving extra speed in some situations.

HEAP tables are the fastest of the lot and are stored in memory. InnoDB and BDB tables are transaction safe, allowing statements to be grouped for integrity of data. InnoDB tables make use of consistent reads, which means results of tables are displayed as they appear after a completed transaction. This isn't always ideal, and you can override this behavior with read locks for updating or for sharing.