MySQL Optimization Top Ten List

I recently had the opportunity to delve deeper into MySQL and how to optimize and generally better my database structure and queries. There’s a lot of information on this topic so I thought i’d share the top ten things that matter to me when creating and querying databases.

1. Make the fields of your tables as small as possible, if you know a varchar field is never going to need a value more than 10 characters, set it to varchar(10).
2. Always use auto Increment IDs in your tables, even if you don’t expect you’ll need them now they always seem to come in handy.
3. Index all the fields you will be querying directly, especially those that you know an exact value for.
4. Normalize the data structure as much as possible, duplicate data is just more for the server to wade through when running a query.
5. If you’re querying something against a number e.g.( WHERE x = 12 ) there’s no need to use quotes around the number value, this actually slows down the query because it needs to convert from a string to a number.
6. Limit the use of LIKE and the % wildcard e.g.(WHERE userName LIKE %Woods%) as this slows the query considerably. If you know that the value is always going to be at the beginning of a field, be sure to only use the wildcard at the end to speed up the query e.g.(WHERE userName LIKE Woods%).
7. Multiple field indexes are good at speeding up queries but be aware that the first field in the index must be the first item from that index searched in the query or the whole thing won’t be used the way you intended.
8. Be aware that there is a cost to indexing, both in time and space. Indexes speed up select queries but slow down deletes and inserts and basically any other queries that involve writing to the DB. The more indexes a table has the slower these write queries will be because the indexes have to be changed with the data. The indexes also take up disk space which may cause a database to reach its disk limit more quickly. The practical implication of both these factors is that if you don’t need a particular index to help queries perform better, don’t create it.
9. Use the EXPLAIN keyword with your query to get important information on the query including the possible keys and estimated rows that will need to be searched to find a result. Especially handy with multiple table queries with joins.
10. Be careful with joins, if used incorrectly they can slow down a query considerably. Values from the preceding table (as listed in the output of EXPLAIN) are used to find rows in the current table. So if you have three tables each with 1000 rows to search you’re actually returning 1,000,000,000 rows of data. Obviously this is too many and indexes should be employed to reduce this number to something more manageable.