Avoid common errors when working with MySQL

Working with the database is often the weakest point in the performance of many web applications. And you can often see how the same rake comes the developers in the design of the database. This set of tips will help you avoid many mistakes and learn a lot of useful things for yourself.

GENERAL

• It is desirable to create a separate user for each individual database.
• The database encoding may be any if it is UTF-8.
• In most cases, it is better to use the InnoDB engine.
• In PHP, it is better to forget about the highly outdated MySQL extension and use PDO or MySQLi if possible.
• Do not open MySQL out without a special need.

It is better to make port forwarding:

ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

• Use the hints from PROCEDURE ANALYSE(), which analyzes the structure of your table and provides tips for optimization. Note that this is only possible if you have real data in your table since the analysis is based on it.

SEARCH AND INQUIRIES

• If you need a single line when accessing the table, then use LIMIT 1. Using LIMIT 1 will be better because the database will stop the selection of records immediately after finding the row instead of choosing the whole table or index.

$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0) { ... }

• Besides the usual row index, you can index any column in the table. This will give you an increase in performance when searching.
• Do not use ORDER BY RAND(). If you really need a random order of rows in your query, there are many different ways to do it differently. You will need to write additional code, but you will get rid of the performance bottleneck. ORDER BY RAND() slows down exponentially as the database grows.
• Avoid using SELECT *. The more data is read from the table, the slower the query, which in turn increases the time spent working with the data warehouse. Also, if the database server is installed separately from the webserver, there will be a long delay in transferring data over the network.

It is better to do in the following way:

$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo «Welcome {$d['username']}»;

WORKING WITH DATA

• If the system is 32-bit, it makes no sense to set the UNSIGNED property for the INTEGER type, since such large numbers are not supported in PHP. But if you are using a 64-bit system then PHP supports large numbers, up to BIGINT with a sign.
• Linked “Foreign keys” tables should have similarities in the key structure.
• It is best to use TINYINT(1) for storing Boolean values.
• Money units are best stored in integers and only work with integers. For example, the number of kopecks to 100 means one ruble.

Working with lines

• The best encoding for most databases is UTF-8.
• Type TEXT can only hold 64 KB. In order to accommodate larger texts, you need to use LONGTEXT.
• In versions of MySQL up to 5.0.3 VARCHAR was limited to 255 characters, but in older versions, the limit is 65535 characters.

WORKING WITH DATES

• When comparing DATETIME and TIMESTAMP, remember to do type conversion:

SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

• Since TIMESTAMP is stored as UNIX_TIMESTAMP, it is possible to set the automatic update of the column.
• DATE, TIME, DATETIME data types are output as rows, so different dates are searched and compared through conversion.

WORKING WITH TRANSFERS

• To enumerate correctly use ENUM type. This is a very fast and compact field type where the values are stored as in TINYINT but displayed as in a string field.
• As for any string, you can set enumerations to the default value.
• The field with enumeration is stored as a number, so the speed is quite high.

WORKING WITH BACKUPS

• It is better to use a backup with additional options -Q, -c, -e:

mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

• -Q wraps names in reverse quotes.
• -c makes a full insert, including the names of the columns.
• -e makes the extended insert.
  The result is a smaller final file, and a slightly faster one is created.

DEBUG

• If queries are slow, you can try to include a log for slow queries in /etc/mysql/my.cnf and then optimize queries via EXPLAIN.
• It is convenient to use the program Mytop to monitor both queries and the server in general.