Basic MySQL Performance Tips
Basic MySQL Performance Tuning
This article highlights some basic MySQLperformance features that you may or may not know about. This is really a beginners article, and does not go into great detail about further optimisations that are possible with MySQL. As a database and web developer for over 10 years I have worked with both Microsoft SQL Server and MySQL and I have compiled a list of essential performance tips which I hope you find useful.
- Simple Techniques To Increase Website Page Speed
Here are some performance tips for ASP.NET Applications which may also apply.
- MySQL :: Download MySQL Community Server
Make sure you keep your version of MySQL Server up to date. The MySQL team frequently release new versions, many of which contain small performance improvements.
MySQL Database Specific
Data Types - Use The Correct Type
Use the most appropriate data type for your fields. Do not use large integer columns when you are only storing 3 or 4 digit numbers. Larger data types require more memory therefore its slower to process. Stick with medium it or small int columns unless you are sure you will need the larger int type.
The same applies to character types, keep your length as small as possible.
Store Text Columns in Seperate Tables
Its better to save your text columns in seperate tables and only query the data when you need it. This keeps the footprint of your frequently used tables small and speeds up insert and update operations.
Add Appropriate Indexes
A frequent cause of slowdown is lack of indexes on a table. Indexes allowing for much quicker searching for data, and eliminates slow table scans. You should identify columns for indexing and add indexes to frequently searched columns.
Choose The Correct Engine - MYISAM or INNODB
MYISAM is fastest, and supports Full Text Indexes, whilst InnoDB is transactional and does not support full text indexes. You can mix and match table types within the same database so design your application to take advantage of the different engines. Use MEMORY tables for storing data that its OK to lose if the server is stopped but do not store large tables in MEMORY.
Use Partitioning To Improve Data Access Speed
Recent versions of MySQL support partitioning. Your data can be split into seperate physical data files and spread onto different disks. This greatly improves many operations such as read speed, update speed and join query performance. Do not partition your data immediately - wait until you have some real data to look at before you identify suitable fields for partitioning. For example in a order management application you may want to partition orders by year. Orders that are rarely searched for e.g. 3 or 4 years ago will not slow down your queries for the latest data.
Follow Normalisation Rules
Ensure your data is well normalised. Database join operations are much faster than table scans, and if you have a large number of rows, your qury will run much faster if you do not have to scan lots of un-normalised text fields. Identify suitable areas for normalisation and follow standard 1, 2 and 3 normal form to optimise
Application Performance Tips
Use The Latest Data Access Layers
Make sure you use the most up to date Data Access drivers for example the current version of the .NET connector for MySQL is 6.2.3. With every connector release, there are usually small performance improvements.
Avoid Select * Queries
Yes, this old chestnut. Avoid SELECT * queries. You will save traffic between application and database by only selecting the columns you need.
Cache Data That Rarely Changes
If you data is static, only update every so often. Choose a good cache strategy to maximise your applications speed.
Assuming all is equal, faster hardware will almost always mean faster database response. So find the fastest server your budget allows. If you are a basic user don't worry, for small tables basic shared hosting is usually more than enough for simple MySQL databases and you can always scale later.
- Ensure your database server has enough RAM. MySQL caches its queries in memory and the more memory available to do this the better. You can configure some settings in the MySQL config to tell MySQL how much RAM to use for the query cache.
- If possible store your database files on fast access disk drives, such as a fibre attached drive array. The faster your disk response times that quicker your database access will be.
- Install a MYSQL cluster for superior performance. Spread processor load across multiple servers for true high end performance. MySQL recently released MySQL Server with Cluster support for Windows platforms.
- Use a dedicated MySQL server , if budget allows, with MYSQL set to use 90% of the memory. A server that also does many other tasks will be unable to dedicated CPU to MySQL all of the time.
- MySQL provide a 64 Bit option - always use this is your operating system and hardware allows as there is a performance improvement.
- If you are installing a new Server, use the latest Core i7 or AMD opteron CPUs. These have multiple cores and large on chip caches, perfect for high performance database number crunching.