Some MYSQL performance tweaks for you (can be used in any SQL RDBMS really). Have fun!

1. Use LIMIT 1 whenever you know you’ll be getting 1 result

If you know you’ll be getting 1 result such as finding a user, adding LIMIT 1 is a good practice, so the db engine will stop looking after the 1st record is found.

2. Avoid SELECT *

The more data is read from the tables, the slower the query will become. Just specify exactly which columns you need!

3. Always have an id field

Use id field as PRIMARY KEY, this is helpful as the db engine uses this id for indexing, clustering, partitioning etc. Helps in better performance.

4. Query cache is your friend

Most SQL servers can cache, i.e. if the same query is executed multiple times, the result is fetched from the cache.

// query cache does NOT work	
result = mysql_query("SELECT user FROM user WHERE signup_date >= CURDATE()");

// query cache works!  
today = date("Y-m-d");  
result = mysql_query("SELECT user FROM user WHERE signup_date >= 'today'");

This also applied to functions such as NOW() and RAND() etc

5. Index fields you lookup by

Indexes can be PRIMARY KEY, UNIQUE KEY & can be any other columns in your table that you will lookup by.

---

Posted 29 Feb 2012