Hey there, folks! Today, we’re going to spend a bit of time talking about MySQL, the most popular relational database management system on the web. Now, while it certainly didn’t get where it is by being either obtuse or difficult to use, it can be a bit finicky sometimes – and there are a few ‘gotchas!’ here and there that can present a challenge to even the most seasoned of administrators.
That’s where we come in. Let’s go over a few useful tidbits about MySQL that everyone should know (but most people seem to forget).
Much Ado About Database Engines
There are two primary storage engines in MySQL, each with its own strengths and weaknesses: MyISAM and InnoDB. It’s important that you select the engine that best fits what you’ll be using your database to do. In broad strokes:
- MyISAM works well for read-heavy applications, but has scaling issues with write-heavy applications.
- InnoDB is more complicated, and tends to be slower than MyISAM when running smaller applications. At the same time, it’s well-suited to scale, and offers support for advanced features like transactions.
Creating a Schema
If you’re doing development work with MySQL, then there’s a good chance you need to regularly create databases – a process that’s tedious, at best, if you don’t remember exactly how to do it (there are a lot of little stumbling points that can easily trip you up if you’re careless). As such, we’re going to provide you with a cheat sheet to which you can refer in the event that you need some guidance.
- To create a new database, type ~/src$ mysqladmin -u root -p create nameofdatabase. You can do this either while logged in or from the shell.
- Switch over to the database with:
- ~/src$ mysql -u root -p
- ~/src$ use nameofdatabase
- Here’s where it gets tricky. You’ll need to create a new user and give them the ability to manipulate the database you’ve created.
- mysql> CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password';
- mysql> GRANT ALL PRIVILEGES ON nameofdatabase.* TO ‘username’@’localhost’ WITH GRANT OPTION;
- In the case of the above commands, note that you can specify IP addresses outside of your local machine, or allow access to anyone within your firewall with %.
- We advise normalizing your database, minimizing the amount of redundant data – for example, don’t use big integers where they aren’t necessary, and don’t declare VARCHAR for fields that will always be the same length.
Avoiding Unoptimized Queries
All lookup operations in a MySQL database are handled in the form of SELECT statements. Suffice it to say, ensuring proper optimization of these statements is a must – even a single rogue SELECT can potentially cause an entire database to chug. Commands such as EXPLAIN, SHOW PROFILE, and SHOW STATUS can be used to manually accomplish this; however, for larger databases, it may be worthwhile to use an automated tool such as Query Profiler.
One word of advice to novice developers/admins? Never use a wildcard (*) to retrieve data from a table. Extract only the data you require for a particular query.
Benchmark and Profile
Continuing along with the previous point, benchmark your database whenever and wherever possible. By simulating a high-stress, heavy-load situation, you can better determine the bottlenecks in your system’s performance (as well as what may be causing them). Combine your benchmarking efforts with a profiling tool, and you’ll also be able to find issues with your configuration, as well as your database.
The Sweet Spot In Indexing
Generally speaking, if you’re naming a column in the WHERE clause of a SELECT query, it should have an index applied to it in order to speed up data retrieval. To further expand upon that, avoid adding indexes to every single column. Adding indexes where you don’t need them, as the more indexes you add, the more memory they’ll require; eventually, you’ll end up hurting your database’s performance.
Generally, index the following:
- Search fields.
- Same column types in JOIN queries.
As a general (and sort of obvious) rule, the smaller the database, the quicker MySQL will be able to manage it. Pretty simple, right? Let’s add something to that:
Just because MySQL can handle larger databases, doesn’t mean it should. If you’re planning on running a massive, enterprise-scale database system, you might want to look at an alternative RDBMS to do it. MySQL performs just fine for websites and smaller development projects, but it tends to run into some pretty significant performance issues at scale.
One Final Word of Advice
There are a ton of different commands, functions, tricks, and shortcuts in MySQL. At the end of the day, however, it’s actually not all that important that you know them. What is important is knowing how configure, restore, and scale your MySQL instance – knowing how to fix database corruption, and what to do in the event that your MySQL installation starts grinding to a halt.
At the end of the day, everything else is really secondary to that.