MariaDB Crash Course - Ben Forta [87]
• There is almost always more than one way to write a SELECT statement. Experiment with joins, unions, subqueries, and more to find what is optimum for you and your data.
• Use the EXPLAIN statement to have MariaDB explain how it will execute a SELECT statement.
• As a general rule, stored procedures execute quicker than individual MariaDB statements.
• Use the right data types, always.
• Never retrieve more data than you need. In other words, no SELECT * (unless you truly do need each and every column).
• Some operations (including INSERT) support an optional DELAYED keyword that, if used, returns control to the calling application immediately and actually performs the operation when there are no more pending operations. While this improves client performance (as control is returned immediately), this option does introduce a risk—if a server were to crash those queries would be lost.
• When importing data, turn off autocommit. You may also want to drop indexes (including FULLTEXT indexes) and then re-create them after the import has completed. Alternatively, you can use ALTER TABLE to temporarily DISABLE KEYS (remember to ENABLE KEYS when you are done).
• Database tables must be indexed to improve the performance of data retrieval. Determining what to index is not a trivial task, and involves analyzing used SELECT statements to find recurring WHERE and ORDER BY clauses. If a simple WHERE clause is taking too long to return results, you can bet that the column (or columns) being used is a good candidate for indexing.
• Have a series of complex OR conditions in your SELECT? You may see a significant performance improvement by using multiple SELECT statements and UNION to connect them.
• Indexes improve the performance of data retrieval but hurt the performance of data insertion, deletion, and updating. If you have tables that collect data and are not often searched, don’t index them until needed. (Indexes can be added and dropped as needed.)
• LIKE is slow. As a general rule, you are better off using FULLTEXT over LIKE.
• Databases are living entities. A well-optimized set of tables might not be so after a while. As table usage and contents change, so might the ideal optimization and configuration.
• And the most important rule is simply this—every rule is meant to be broken at some point.
Summary
In this chapter, you reviewed some important tips and notes pertaining to MariaDB performance. Of course, this is just the tip of the iceberg, but now that you have completed the MariaDB Crash Course you are free to experiment and learn as you best see fit.
Appendix A. Getting Started with MariaDB
If you are new to MariaDB, here is what you need to know to get started.
What You Need
To start using MariaDB and to follow along with the chapters in this book, you need access to a MariaDB server and copies of client applications (software used to access the server).
You do not need your own installed copy of MariaDB, but you do need access to a server. You basically have two options:
• Access to an existing MariaDB server, perhaps one by your hosting company or place of business or school. To use this server you will be granted a server account (a login name and password).
• You may download and install a free copy of the MariaDB server for installation on your own computer (MariaDB runs on all major platforms including Windows and Linux).
* * *
Tip: If You Can, Install a Local Server
For complete control, including access to commands and features that you will probably not be granted using someone else’s MariaDB server, install your own local server. Even if you don’t end up using your local server as your final production DBMS, you’ll still benefit