Skip to main content

MySQL best practices

For more details and explainations, take a look at http://code.tutsplus.com/tutorials/top-20-mysql-best-practices--net-7855
The below is my brief for you to easily memorize.

1. Optimize Your Queries For the Query Cache

DON'T:
    $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
DO
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= {$today}");

2. EXPLAIN Your SELECT Queries

Using the EXPLAIN keyword can give you insight on what MySQL is doing to execute your query.

3. LIMIT 1 When Getting a Unique Row

DON'T
/*state is the unique field*/
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
DO
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");

4. Index the Search Fields

If there are any columns in your table that you will search by frequently, you should almost always index them.
ALTER TABLE `user` ADD INDEX (`username`);  

5. Index and Use Same Column Types for Joins

6. Do Not ORDER BY RAND()

DON'T
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
DO
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1); //$rand is the offset for the next query.

$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. Avoid SELECT *

Always specify which columns you need when you are doing your SELECT's.
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");

8. Almost Always Have an id Field

9. Use ENUM over VARCHAR

If there is a column named status and it has 4 statuses only ("active", "inactive", "pending", "expired") then user ENUM type instead VARCHAR.

10. Get Suggestions with PROCEDURE ANALYSE()

PROCEDURE ANALYSE() will let MySQL analyze the columns structures and the actual data in your table to come up with certain suggestions
  • Use it when you have numerous data.

11. Use NOT NULL If You Can

Unless you have a very specific reason to use a NULL value, you should always set your columns as NOT NULL.

12. Use EXIST over IN for subquery

I highly recommend you to user EXIST & NOT EXIST in subquery for the large table.
Explaination
In short:
  • Use EXISTS when the subquery results is very large.
  • Use IN when the subquery results is very small.

13. Prepared Statements

The benefits of using prepared statements are both performance and security.
DON'T
$select = $this->select()->where("UserID = {$userid}");
$row = $this->fetchRow($select);
DO
$select = $this->select()->where("UserID = ?", $userid);
$row = $this->fetchRow($select);

14. Store IP Addresses as UNSIGNED INT

Use INET_ATON() (Mysql) or ip2long to convert an IP to an integer.
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

15. Fixed-length (Static) Tables are Faster

VARCHAR, TEXT, BLOB are not fixed-length
Convert a VARCHAR(20) field to a CHAR(20) field, it will always take 20 bytes of space regardless of what is it in.

16. Vertical Partitioning

Example 1: You might have a users table that contains home addresses, that do not get read often. You can choose to split your table and store the address info on a separate table. This way your main users table will shrink in size. As you know, smaller tables perform faster.
Example 2: You have a "last_login" field in your table. It updates every time a user logs in to the website. But every update on a table causes the query cache for that table to be flushed. You can put that field into another table to keep updates to your users table to a minimum.

17. Split the Big DELETE or INSERT Queries

If you need to perform a big DELETE or INSERT query on a live website, you need to be careful not to disturb the web traffic.
DO
while (1) {  
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");
    if (mysql_affected_rows() == 0) {
        // done deleting
        break;
    }
    // you can even pause a bit
    usleep(50000);
}

18. Smaller Columns Are Faster

If a table is expected to have very few rows, there is no reason to make the primary key an INT, instead of MEDIUMINT, SMALLINT or even in some cases TINYINT.

19. Choose the Right Storage Engine

  • MyISAM is good for read-heavy applications. Lock level: table.
  • InnoDB tends to be a more complicated. Lock level: row.

20. Use an Object Relational Mapper

  • ORM's can also batch your queries into transactions, which operate much faster than sending individual queries to the database.
  • ORM's are great for "Lazy Loading." It means that they can fetch values only as they are needed.
  • ORM for PHP is Doctrine.

Comments

Popular Posts