An outline should read as fluidly as a story. Don’t write a college outline. Instead, focus on the most important things you want to say and then state and explain those things, using examples and data to demonstrate your points. Use sidebars to discuss relevant but tangential topics.
Title: Making Optimal Use of MySQL( or maybe” Optimizing MySQL Queries”)
Goal
After reading this article, you should understand how to design you MySQL databases and write your queries so that your applications are efficient.
Required Knowledge
I will assume that you have very basic knowledge of MySQL, the SQL language, and relational database principles such as data normalization. You will not need access to your MySQL server’s configuration settings to make use of these techniques.( I will delve into MySQL server tuning next time.)
Sample Data
Introduce the” music” database that will be used in concrete examples. The schema for the tables will appear once as a figure. The database will contain four tables: albums, artists, tracks, and genres.
Article Body( the real HOWTO stuff)
Database Design Tips
1. Only store what you need.
While it may seem like common sense, it’s important to think about the data you need to store when you design a database. Needlessly storing information which can be constructed later may result in decreased performance.
2. Normalize your schema
Having a set of normalized tables will help to reduce duplication of data( and the related disk space). It will also give you a lot of flexibility in writing queries.
3. Use Indexes where appropriate
Indexing the right data is probably the single most important thing you can do to increase the performance of queries in MySQL. Because it’s so important, I’ll dig into how you should decide which columns to index.
A.How MySQL Uses Indexes
B.How Indexes Work
C.Using EXPLAIN
4. Don’t overuse indexes
Having sung the praises of indexes, you might feel compelled to index every column you might have an a WHERE clause. Don’t. The performance boost that indexes can give your SELECT queries does not come without a price. Indexes slow down INSERT, UPDATE, REPLACE, and DELETE queries.
5. Compress BLOB data
Because MySQL uses normal files for tables and indexes, your operating system, presumably Linux, will cache frequently accessed data( tables rows and indexes) in RAM to improve performance. So it’s important that you do what you can to reduce the size of the rows in your tables–especially tables which contain BLOB data which may be quite large.
6. Data Retrieval Tips
A.Only ask for what you need
Just like saying” only store what you need”, this may seem like common sense. But often times it is overlooked. Don’t use” SELECT*” on a table with 20 columns if you only need data from 2 of them. Often times I use” SELECT*” when coding because I lazy or I just don’t know which rows I’ll need quite yet. But I don’t always think to go back and make those queries leaner. The end result is that MySQL goes to the effort of retrieving far more data than I actually need. That effort amounts to wasted CPU cycles.
B.Ask Your DBA for Help
slow query log
optimize tables periodically
replication
Conclusion
Using the tips and techniques I’ve presented, you can go a long way toward having a MySQL-based application which performs well as the size of your data and your user base grow.
Next time I’ll discuss what you can do to tune the MySQL server itself, assuming you have access to the server.
Sidebars
1. The Query Optimizer
2. Understanding Join Types
Loading ...
No Comments
No comments yet.