Few days back I started writing my article series on mysql performance optimization. Unfortunatly I was unable to continue the stream. This is my second attempt to do the same and will my try level best not to discontinue. I will be trying to keep things concise, comprehensive and focussed. Please let me know if I misunderstood anything. In this article I will be discussing about the basic things that we should consider while planning for a database.
Many people thinks that performance depend only on the code and the query that we write. But in reality database design comes first and the code is next. If we have a database with good physical design it will be easy to write code, manage, scale, and optimize.Our main focus is to reduce the table size, so that the data will fit in memory.One of the main reason for drastic performance decrease is the size of table or query result does not fit into memory. At that time mysql will perform multiple IOs to return the result.
The key points for improving performance are
- Choose the right datatype
- Choose the right data width
- Choosing and Using the indices
- Partitioning if the table is sufficiently big.
Choosing the right datatype make sure our data will consume small amount of memory as possible. For this we should have a clear idea about the growth rate of our database. For example if our department table can have maximum of 100 records weshould be using tinyint since it can hold upto 255. Like wise if activity_log table will never have more than 4 billion rows we need not use bigint for its primary key. Keeping the datatype as small as possible will give an exponential performance boost in large databases.For character datatypes it will be better to use varchar instead of char(200) or something like that.
If you have a frequently accessed table that have many nullable columns consider splitting them in to two tables in a one-to-one relationship.It will improve the performance since the table size will be small and hence will fit in to the memory.
Another important thing in performance optimization is choosing and using right indices. Note that I have specified choosing and using separatly, since by adding an index on a column, mysql dont necessarily use them for many reasons. Choosing a good index will improve performance in many ways. At the same time adding a bad index or redundant index will decrease the performance. Each insert into the table will need a rearrange of the index tree.Another important factor is index selectivity or cardinality.How large portion of rows matches that particular value or range, which means if more than 30% of your indexed column contains the same value in where clause, mysql optimizer wont onsider using the index.Oftentimes We will need to manually make sure the optimizer is using our indices.It really depend on query optimization which I will detail in my next article.
Another thing that will determine the application speed is the storage engine of tables. Usually MyISAM storage engine is faster for selects or for faster insertions (Not for both since in MyISAM dml statements causes table level locking which will lead to blocking of all pending reads) whereas InnoDB storage engine are mostly used in transactional tables.InnoDB has many advantages over MyISAM like full transactional support, support of foriegn key, etc. MyISAM is more suitable in web applications since most web applications are non transactional and the speed of internet won't overcome the query execution time.