22 MySQL best practices to follow as a developer in 2019 - wpDataTables

Navigating the waters of data management? Dive into the MySQL best practices that are all the rage! You’re in a place where each line of code counts.

Picture this: your database humming like a well-oiled machine. That’s what we’re aiming for. And whether you’re a fresh face or an old hand at MySQL, everyone needs a compass to steer by.

By the time we drop anchor, you’ll be decked out with the essentials. We’re talking database optimization, letting you sidestep performance pitfalls like a skilled sailor. I’ll show you how backend buccaneers keep their treasure trove of data locked down tight with MySQL security tactics. Because, let’s face it, data breaches are more than just a bad day at sea.

We’ll be charting a course through SQL query performance techniques, setting sail past indexing strategies, and taking a deep dive into data normalization. Your takeaway? A booty of knowledge that’ll put the wind in your digital sails. Ready? Let’s make some waves.

If you are looking for a nice and easy way to display a large number of rows and columns to visitors of your WordPress website with colorful, informative, and responsive interactive tables and charts you can use it with wpDataTables plugin, check the pricing and all the functionalities it gives here.

1. Always use proper datatype

One of the most important MySQL best practices is to use datatypes based on the nature of data. Using 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.

2. 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).

3. 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.

4. 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.

5. 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.

6. 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.

**7. 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.

8. 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.

9. 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.

10. 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

11. 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.

12. 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) {
// …
}

13. 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

14. 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.

15. 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.

16. 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.

17. 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.

18. 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.

19. 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.

20. 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.

21. 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

22. 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.

FAQs about MySQL best practices

How can I optimize my SQL queries for better MySQL performance?

Optimization’s the name of the game. You gotta think like a chess player – strategize. Planning your moves means understanding indexes and avoiding wildcards at the start of your LIKE clauses. That’s like kicking off your query with a turbo boost.

What’s the best approach to indexing in MySQL?

Indexing, oh boy, it’s like mapping a treasure hunt. The trick is to index columns often searched and filtered upon. But remember, overindexing can backfire. It’s about striking a balance, like a good barbecue – enough heat to cook, but not so much you burn the meal.

Should I normalize my database, and to what extent?

Normalization is like a pantry organization – too little, and you’ve got chaos; too much, and your efficiency tanks. Aim for the sweet spot. Third normal form often hits the mark, keeping redundancy low and retrieval swift without overcomplicating relationships.

How do I ensure MySQL database security?

Lock it down hard. Start with strong passwords and user privileges on a need-to-use basis. Regular updates and a watchful eye on exposed ports are non-negotiable. Treat your data like a state secret; limit who knows what, and never let your guard down.

Which storage engine should I use in MySQL?

It’s a bit like choosing a vehicle – it depends on the journey. InnoDB is your all-terrain, reliable choice; it’s got your back with transaction support and crash recovery. Meanwhile, MyISAM’s like a classic car – simpler, but lacks some of the modern bells and whistles.

How do backup and recovery work in MySQL?

Always have an escape plan. Run regular backups; think of them like life jackets on a boat. And when disaster strikes, recovery should be ready to roll. Tools like mysqldump or Percona XtraBackup are your emergency crew, waiting to rescue your data from the depths.

Is MySQL replication difficult to set up?

Replication’s not rocket science, but it needs a steady hand. You’re basically cloning your data across different databases. Keep unique server IDs and binary logging switched on, and make sure your data’s in sync. Then, it’s smooth sailing as your data flows from master to slaves.

How do I tune the performance of my InnoDB engine?

InnoDB tuning is like tuning a guitar – it’s got to be just right. Consider your buffer pool size; that’s your main memory act. And don’t ignore your log file size; keeping it properly scaled ensures your database transactions hit the right notes smoothly.

Why is ACID compliance important in MySQL?

ACID compliance is like the rulebook of data integrity – it keeps the game fair and your data in check. Without it, you’re gambling with transaction reliability. With it, you can bank on consistency, armor against crashes, and bulletproof your rollback capabilities.

Can MySQL handle big data effectively?

Think of MySQL like a trusty shire horse – strong up to a point, but not a racehorse. It manages big data decently when designed with forethought. Partition your tables, keep indexes lean, and use summary tables. It’s about being smart with your resources – plan well, and scale wisely.

Ending thoughts

So, we’ve cruised through the winding rivers of MySQL best practices. Feels like we caught a glimpse of the database Northern Lights, doesn’t it? Quite the spectacle, learning the ropes of query optimization and how to sweet-talk those indexes into shape.

Before we part ways, remember:

Backups are your safety net — set ’em up.
InnoDB is your reliable pal for most adventures.
Normalization? It’s like seasoning; just the right amount.

Now, you’re prepped with the knowledge to keep your data shipshape. Database management isn’t just about keeping your head above water; it’s about sailing with confidence. And hey, this isn’t “goodbye.” It’s just a “see you later” in the digital realm, where streamlined SQL performance and solid security measures are the norm, not the exception.

Cast off the bowlines and make those MySQL databases hum with efficiency, and may your data always flow as smoothly as a perfect cup of morning joe. Safe travels, data voyagers!

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