MySQL – Some Precautions that could Make a Difference

From the title, you might feel that this is an old topic. But after you read through, please go and recheck your database system and your queries. You might find that most of the points have not been taken care of.

Technology is growing at a fast pace. So you don’t have the time to learn everything and then put it into practice, like we used to do 10 years back. Same applies when using Mysql in your projects. If you know how to connect to the database and write a few basic queries, you can get started to use it in your programs. But you might run into few performance issues, when jumping straight into it like this. Following are few tips to avoid this.

What should be taken care of while indexing ?

Avoid UNIQUE INDEXES, unless it is very necessary. UNIQUE INDEXES would disable InnoDB change buffering. Try to use regular INDEX always. Also make sure to remove all unnecessary indexes on a table, especially when the table is large. Stick the Indexing to those fields, which are part of your query condition section.

What should be the datatype of a Primary Key ?

It is always better to use either an INT or BIGINT datatype than any others. Otherwise it will have a performance curve that degrades much faster. Having no PRIMARY KEY will also affect performance negatively. When you replicate a database on mysql, A table without a primary key may cause delay in the replication.

Which MySQL Engine : InnoDB or MyISAM ?

MyISAM can be faster at inserts to the end of a table, but it has both table locking (limiting updates and deletes) and uses a single lock to protect the key buffer when loading data to/from disk, resulting in contention. It also does not have the change buffering feature described just below. The best way to select the engine is : When there is frequent reading, almost no writing and when there is Full-text search in MySQL <= 5.5, use MyISAM. In all other cases, use InnoDB (especially when the tables are large)

Sort and bulk load data into tables.

Inserting in order will result in fewer page splits (which will perform worse on tables not in memory), and the bulk loading is not specifically related to the table size, but it will help reduce redo log pressure.

If bulk loading a fresh table, try to delay creating any indexes next to the PRIMARY KEY. If the indexes are created after the data is loaded, then InnoDB is able to apply a pre-sort and bulk load process which is both faster and results in typically more compact indexes. This optimization is applicable since MySQL 5.5.

InnoDB Configurations that can be tuned to your advantage

InnoDB has change buffering that caches changes, to secondary index entries, when the relevant page is not in the buffer pool, thus avoiding expensive I/O operations by not immediately reading in the page from disk. The buffered changes are merged when the page is loaded to the buffer pool, and the updated page is later flushed to disk. It was greatly improved in MySQL 5.5, so it is time to upgrade if you haven’t.

Use innodb page compression. For some workloads (particularly those with lots of char/varchar/text data types) compression will allow the data to be more compact, stretching out that performance curve for longer. This will be useful particularly when you have an HDD or SSD, which is of lower capacity. InnoDB page compression was improved a lot in MySQL 5.6, courtesy of Facebook providing a series of patches.

Joins or Subqueries ?

The main advantage of a join is that it executes faster. The performance increase might not be noticeable by the end user. However, because the columns are specifically named and indexed and optimized by the database engine, the retrieval time almost always will be faster than that of a subquery. There are also inner and outer joins, left and right joins, full joins and cross joins. A disadvantage of using joins is that they are not as easy to read as subqueries. Another disadvantage is that it can be confusing as to which join is the appropriate type of join to use to yield the correct desired result set. You can understand the logical thinking of the programmer by looking at the style of writing joins or queries in general.

Mysql Views and performance

Badly designed VIEWs will affect the performance of an application. Simple views are expanded in place and so do not directly contribute to performance improvements – that much is true. However, indexed views can dramatically improve performance. Views can have a clustered index assigned and, when they do, they’ll store temporary results that can speed up resulting queries. But most of the time Views are primarily used for convenience and security, not for speed improvements. Using Views will all

Storage and hardware effects on performance

Memory, which is often underscored, has significant effects on the performance. If SHOW ENGINE INNODB STATUS shows any reads under BUFFER POOL AND MEMORY and the number of Free buffers is zero, some memory additions will be of great advantage (assuming you have sized innodb_buffer_pool_size correctly on your server. )

The type of HardDrive used also has an impact on performance. As the table gets bigger, a lot of additional I/O operations have to be performed. The amount of I/O operations that can be performed per second, will thus determine the speed of output. While a normal hard drive can do 200 operations per second (IOPS), an SSD can go beyond 20K operations per second.

To Conclude….

Even though most of us would have read about these points while we started off with MySQL, or at some moment of our Programming journey, we take serious notice of it only when we are affected by a slow performance or complaints from the users. These are precautions, which when taken at the start of development, would do you and your clients a world of good. Happy coding!!

Know More About This Topic from our Techies