Some MYSQL performance tweaks for you (can be used in any SQL RDBMS really). Have fun!
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.
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
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