MySQL best practices to follow as a developer

MySQL best practices to follow as a developer

MySQL is the second most popular open-source relational database management system in the world. It is used worldwide because of its consistently fast performance, high reliability, and ease of use. This article presents some of the MySQL best practices.

With them, you can practice SQL and set yourself some SQL exercises, learn about SQL limit and SQL practice online, as well as about the most common SQL practice problems.

It will help you understand row number SQL, SQL top, MySQL limit offset, and SQL online practice.

Always use proper datatype

One of the most important MySQL best practices is to use datatypes based on the nature of data. Using the irrelevant datatypes may consume more space or lead to errors.

For example: Using varchar (20) instead of DATETIME datatype for storing date time values will lead to errors in date time-related calculations. Also, it is possible that invalid data will be stored.

Use CHAR (1) over VARCHAR (1)

VARCHAR (1) takes extra bytes to store information, so if you string a single character, it better to use CHAR (1).

Use the CHAR datatype to store only fixed length data

For example: If the length of the data is less than 1000, using char (1000) instead of varchar (1000) will consume more space.

Avoid using regional date formats

When using DATETIME or DATE datatype, always use the YYYY-MM-DD date format or ISO date format suitable for your SQL Engine. Regional formats like DD-MM-YYYY or MM-DD-YYYY will not be stored properly.

Index key columns

It is desirable that the query returns the result fast, so make sure to index the columns which are used in JOIN clauses.

In case you use UPDATE statement involving more than one table, make sure to index all the columns which are used to join the tables.

Do not use functions over indexed columns…

… because then the index loses its purpose.

For example, suppose you want to get data where the first two characters of customer code are AK. Write:

SELECT columns FROM table WHERE customer_code like ‘AK%’

and do not write

SELECT columns FROM table WHERE left (customer_code,2)=’AK’

Why? The first example will make use of the index, which will result in a faster response time.

Use SQL SELECT * only if needed

Following on the list of the MySQL best practices, do not just blindly use SELECT * in the code. If the table has many columns, all will be returned. This will slow down the response time, especially if you send the result to a front-end application.

Instead, explicitly type out the column names which are actually needed.

Note: remember that all SELECT statements require a WHERE clause.

Use ORDER BY clause only if needed

If you want to show the result in the front-end application, let it ORDER the result set. If you do this in SQL, the response time may be slowed down in the multi-user environment.

Choose a proper Database Engine

If you develop an application that reads data more often than writing (e.g. a search engine), choose MyISAM storage engine.

Choosing the wrong storage engine will affect the performance.

Use EXISTS clause wherever needed

For checking the existence of data, use EXISTS clause which is faster in response time. For example, use:

If EXISTS(SELECT * from Table WHERE col=’some value’)

Do not use :

If (SELECT count(*) from Table WHERE col=’some value’)>0

EXPLAIN your SELECT queries

If you use the EXPLAIN keyword, you can get insight on what MySQL is doing to execute your query. This can help you detect problems with your query or table structures (e.g. bottlenecks).

An EXPLAIN query results in showing you which indexes are being utilized, how the table is being scanned, sorted, etc.

All you have to do is add the keyword EXPLAIN in front of a SELECT query (preferably a complex one with joins). Also, if you use phpmyadmin for this, your results will be shown in a nice table.

Use LIMIT 1 when getting a unique row

Sometimes you know in advance that you are looking for just one row when querying your tables. For example, you might be fetching a unique record, or you might just be checking the existence of any number of records that satisfy your WHERE clause.

In such cases, you will want to use the MySQL limit function to increase performance. Here is another of the MySQL best practices: simply add LIMIT 1 to your query. This way the database engine will not have to go through the whole table or index. It will stop scanning when it finds just 1 record of what you are looking for.

// do I have anyusersfrom Alabama?

// what NOT to do:
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
if (mysql_num_rows($r) > 0) {
// ...
}

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

Index and use the same column types for joins

Another vital tip of MySQL best practices – if your application has many JOIN queries, make sure that the columns you join by are indexed on both tables. This affects the internal optimization of the join operation by MySQL.

Also, the joined columns need to be the same type. For example, if you join a DECIMAL column to an INT column from another table, MySQL won’t be able to use any of the indexes. Even the character encodings need to be the same type for string type columns.

// looking for companies in my state
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");

// both state columns should be indexed
// and they both should be the same type and character encoding
// or MySQL might do full table scans

Hide MySQL from the Internet

Experienced database administrators and security personnel know it – never host the database under the Web’s server’s root.

For Web-enabled applications, MySQL should be hidden behind a firewall. Communication should be enabled only between application servers and your Web servers.

Another option is to use MySQL skip-networking. When it is enabled, MySQL only listens for local socket connections and ignores all TCP ports.

Use the smallest data types possible

Let me tell you a story. When I was attending college, the philosophy was that “memory is scarce”. Those were the days of 256 MB hard drives. Nowadays, no one seems to care one iota about memory or hard drive space. The new philosophy is that “memory is cheap”. It might be true in dollar terms, but reading large data types still takes longer than reading smaller ones. Large data types require more disk sectors to be read into memory.

The moral is, ignore the temptation to immediately jump to the largest data type when you design your tables. Think about using an int instead of a bigint.

Also, avoid largechar (255) text fields when a varchar or smaller char is enough.

If you use the right data type, more records will fit in memory or index key block. This leads to fewer reads and faster performance.

Take advantage of query caching

Query caching is one of the most effective methods of improving performance. Most MySQL servers have it enabled by default.

The query cache stores the text of a SELECT statement together with the corresponding result set. If the server later receives an identical statement, it will retrieve the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

However, great as it is, query caching has its limitations. Take the following statement:

The problem here is that queries contain certain non-deterministic functions, like NOW() and RAND(). MySQL cannot calculate such functions in advance, so they end up not being cached.

Fortunately, there is an easy solution to that: you can store the function results in a variable.

Do not edit dump files

Dump files are very deceiving and can cause corruption. Why? If you have ever seen the dump files created by mysqldump, you will agree they look like regular, harmless text files. That is why most people edit them in a standard text editor, which causes corruptions to appear.

If you have ever tried editing dump files, you quickly learned they are anything but a text file. So, the only guaranteed way to avoid problems is to leave the dump files alone.

Use the MyISAM block size setting

The setting for block sizes in the indexes of MyISAM tables is one of the MySQL best practices. It can be found in the .MYI files in the key buffer, as well as the disc. The setting looks like this: myisam_block_size.

It has a default value of 1k. It is quite small to be optimal on a modern system. Larger size blocks are used by most file systems. And we know that writing a single index block requires a read and then a write. The operating system will never have to write to the underlying disc as long as the block size is equal to or greater than the file system block size.

Turn on delay_key_write

Delay_key_write is turned OFF by default. There is a reason for that. If you experienced a crash in the middle of the project, your database could become corrupt.

So, why would you want to turn it on? The reason is simple. Because the delay_key_write ensures that the database will not flush the MyISAM key file after every single write. Therefore, if you are doing another write in the near future, you will be saved quite a lot of time.

Here is another cool tip we chose from MySQL best practices: Turning on delay_key_write is different for every version. To see how to turn it on in a specific version, consult the official MySQL site manual.

Use Stack Trace to isolate bugs

Following on these MySQL best practices, this tip is borrowed from Sky SQL because it is just too simple and too convenient to be left out.

MySQL stack_trace can be used to isolate various bugs. Instead of digging into all possible uses, the programmer might want to take note on how easily a null pointer can ruin your code.

With this Sky SQL tip, spotting, tracking, and fixing become way easier.

MySQL changing the ROOT password

Changing the ROOT password might seem basic, but knowing how to do it is equally important for your home operating system as well as on MySQL servers.

Sometimes it happens that overzealous beginners and absent-minded experienced users cannot figure out why they cannot change certain settings and are returning errors. It can be as simple as giving yourself ROOT access. A user shouldn’t even have to google it.

Here is how to set up the ROOT password and how to change a user’s ROOT password:

//Straightforward MySQL 101
$mysqladmin -u rootpassword [Type in selectedpassword]
//Changingusers ROOT password
$mysqladmin -u root -p [type oldpassword] newpass [hit enter and type new password. Pressenter]
//Use mysqlsqlcommand
$mysql -u root -p
//prompt “mysql>” pops up. Enter:
$use MySQL;
//Enter usernameyouwant to change the password for
$update user set password=PASSWORD (Type new PasswordHere) where User = 'username';
//Don'tforget the previoussemicolon, nowreload the settings for the user'sprivileges
$flush privileges;
$quit

Fix your configuration files

MySQL Tuner is a Perl script that can somehow optimize your performance by suggesting changes to your configuration files.

If some tips and tricks in MySQL are amazingly convenient, tools like MySQL Tuner are a godsend that deserves to be in a category all their own.

It is something that can be used by novices and pros alike. MySQL Tuner is not a specific tip for MySQL, so there is a variety of tweaks and mods that can be applied. The more you use it, the more tweaks you can learn to apply for your own use.

It may seem intimidating at first, but that is why you can find the manual –man db as well as all notes, necessary reading and settings on the official project homepage. With all that, MySQL Tuner should quickly become your go-to tool for speeding up MySQL and testing your configuration files.

Ending thoughts on the best MySQL practices

In the world of computer science, MySQL is undoubtedly one of the most important and influential programs to ever appear. It is so involved and versatile, that just when it looks like all the possible tricks and shortcuts have been discovered, someone else chimes in with a new way to use a new argument or setting.

We have outlined only MySQL best practices that everyone should know. But, the more you use MySQL, the more you will use these. You can practice SQL online and set yourself SQL tests. You will then develop your own tricks, and find your own top tips that you can rely on such as limit SQL, SQL row number, and understand why you should select MySQL in the first place.

If you enjoyed reading this article about MySQL best practices, you should also read these:

Up Next:

Best WordPress Booking Plugins in 2018

Best WordPress Booking Plugins in 2018