intro
In this comprehensive guide, we'll clarify common MySQL facts and myths and answer some of the most frequently asked questions in the MySQL realm. From MySQL's storage engines to its server performance and optimization tips, join us as we delve into the fascinating world of this powerful database management system.
If you’ve ever worked with any database management system (DBMS), you surely know how many people ask questions about databases and their performance. Have a look into a bunch of questions inside of StackOverflow tagged with MySQL to begin with – are you surprised? Don’t be. In this blog, we’ll walk you through the answers of the most frequently asked questions about the MySQL database management system.
Why Should I Be Concerned?
Before walking you through FAQs related to MySQL and their brothers Percona Server and MariaDB, we’ll answer a simple question – why should you be concerned about questions concerning MySQL? The answer here is pretty simple – you should be concerned because by knowing the answers to these questions you will be able to work with your databases more quickly, efficiently, and eliminate errors that may arise due to your use of the database.
Fiction Surrounding MySQL: Debunking Myths About the MySQL Database Management System
Frequent readers of our blog will know that we like to dig into facts – facts are also very important before even attempting to explain any topic, so bear with us while we do that:
| Statement | Fact or Fiction? |
|---|---|
| MySQL is not a fit for big data. | Fiction. One of the primary storage engines available in MySQL – InnoDB – is known as a high-performance and a high-reliability storage engine and it offers many features to advance our work with big data sets within the RDBMS. |
| You can’t have many indexes on a table in MySQL. | Fiction. One table within MySQL supports up to 64 indexes – that’s more than enough for pretty much any user. |
| There are no use cases of the MyISAM storage engine within MySQL any more. | Fiction. MyISAM is effectively rendered as obsolete, but it’s power to store the row count of the table within itself shouldn’t be discounted – COUNT(*) queries will always be faster on MyISAM than on InnoDB. |
| Older versions of MySQL have had more data types one could choose from than now. | Fact. As of MySQL 8, data types have been deprecated. |
To know more about facts and fiction within MySQL and other database management systems, make sure to have a look into the YouTube channel of our friends Database Dive – but now that you’ve warmed up, let’s get into answering the FAQs!
What kind of storage engine should I use?
Depending on the flavor of your MySQL Server, use InnoDB or Percona XtraDB. These two storage engines are essentially brothers and do the same thing, but Percona XtraDB comes with more enhancements than InnoDB does.
Adding an index on a bigger table slows down after a while. Why?
The answer lies within the configuration of your MySQL storage engine and its buffer pool – make sure to raise the InnoDB buffer pool size up to 60 to 80% of available RAM in your system.
My SELECT SQL queries are slow. Why?
SQL queries can slow down due to one or more of the following reasons:
How do I get the recommended buffer pool size according to the data I have in my DB instance?
There’s a SQL query built just for that. Have a look!
When to use an index?
You should use an index on your columns whenever you feel like your SELECT query performance is nose diving or when you have to scan through a lot of data.
What kind of an index should I choose?
The most frequent type of index you should choose is a B-tree index (an ordinary index), but there are a couple of other scenarios:
When to use MyISAM?
Use MyISAM whenever you have the need to count rows in a table. MyISAM stores the internal row count inside of its metadata, other storage engines do not.
What does the secure-file-priv setting do?
This parameter lets you tell MySQL where to load data when using LOAD DATA INFILE queries.
When will MySQL 5.7 be EOL’ed?
MySQL 5.7 end of life will occur at October 2023
What’s the most important parameter in InnoDB?
The most important parameter is the buffer pool because it stores data, indexes, MVCC data, and metadata.
How to make INSERT queries faster?
Make use of bulk INSERT features: specify INSERT (1,2,3),(4,5,6) instead of INSERT after INSERT, or switch INSERTs to LOAD DATA INFILE entirely – keep in mind that INSERTs always come with overhead which can be avoided with LOAD DATA INFILE.
How to see the structure of a MySQL table?
Run a DESCRIBE SQL query.
How to make INSERT queries faster without switching them to LOAD DATA INFILE?
Utilize a bulk INSERT feature like discussed above or disable autocommit (autocommit=0) while your data is imported then re-enable autocommit by running a COMMIT query.
What character set to use for my tables?
Use utf8mb4 unless you need support for specific languages.
Is utf8mb4 the same as utf8? Why are there two character sets?
No, utf8 is not the same as utf8mb4. UTF-8 should support up to four bytes per character, while MySQL’s utf8 only supports up to three. That’s why utf8mb4 exists.
How to protect MySQL from SQL injection?
Don’t pass user input to a database, parameterize your statements, and look into MySQL Enterprise-level offerings (e.g. MySQL Enterprise Firewall) if possible.
When to partition MySQL?
Partition your tables whenever you feel like you have a lot of data – keep the logic “partition is a smaller table” in mind and you should be good to go. Look into the documentation for partitioning types.
How to take a look into what indexes / partitions my query is using?
Use the EXPLAIN SQL query.
How to terminate a long-running SQL query properly?
Use a SHOW PROCESSLIST query to observe all of the processes in your database, then use a KILL query to terminate a query.
How to clean the InnoDB storage engine?
Your data will now be stored in .frm and .ibd files, but not in ibdata1 itself.
Have a look at the Q&As above – it should definitely answer at least some of your questions. Should you have more questions, please refer to the documentation of MySQL, Database Dive, and our blog for more information.
DbVisualizer for MySQL
If you find yourself using any database management system, also keep in mind that database clients like DbVisualizer can help you manage the security and performance side of your database instances. DbVisualizer is a very popular SQL client used by NASA, Google, Tesla, Meta, and a wide array of other companies, so it certainly won’t let you down. Each feature available in DbVisualizer was carefully crafted to solve real-world problems and issues, so grab a free trial of DbVisualizer today and let the tool do its magic!
Summary
In this blog, we’ve walked you through some of the most frequently asked questions in the MySQL realm. We hope that this blog has been informative and useful, come back to our blog and check what our friends over at Database Dive are doing from time to time, and until next time.
