MySQL database Index – Part 1

Why MySQL database index does not help improve performance? And how to use New Relic to find out the cause.

I went through some of the sql tutorial on how to create index in MySQL. It took very little time for me to create my first MySQL index, and job done. That’s what I thought…

After 1 month launching the API, we decide to integrate it with existing sites.

Table A starts from less than 500 rows to 370000++ rows after importing all data from different sites. Immediately the response time of any functions that SELECT from the table can go up to 3000 ms (that’s almost 3 seconds).

After created the index, nothing seems to improve, after observing New Relic for sometime, it showing analysis of index using “PRIMARY”. The query is not using the newly created index.

New Relic Query Analysis before MySQL database index hint.

New Relic is showing very awful and horrible average response time!!

New Relic slowest average response time before MySQL database index hint.

 

Thanks to some tips from a MySQL guru, it seems that MySQL will auto decide which index to use to execute your query if you didn’t specify any.

By using MySQL’s “Index Hint Syntax“, I’m now able to force the sql query to execute with the specified index. :)

SELECT * FROM table1 USE INDEX (indexname1,indexname2)
  WHERE col1=1 AND col2=2 AND col3=3

Ran the same query on MySQL Workbench and immediately i got results with huge performance differences.

Executing MySQL database SELECT statement with and without MySQL database index hint.

After the deployment of the new code by adding “USE INDEX“, New Relic is showing some nice average response time. NO MORE 3000 ms response time!!

New Relic slowest average response time after MySQL database index hint.

 

 

Even though there is huge performance improvement, but I feel that there is room for improvement. After all, New Relic suggest that i can do more. Here is what New Relic suggest. :)

New Relic Query Analysis after MySQL database index hint.

MySQL MyISAM vs. InnoDB: Batch Insert Senario

Recently the company hired a Business Analyst, and has requested some reports which cause Heavy Disk Queue to the Master Database (MSSQL). :(

Receiving lots of complains from users that they can’t perform their duties in the management dashboards. (Disk Queue is over 100, where as normally is only 0.2 to 2 max during peak hours).

Had a discussion with the “General”, he explain that there should be a separate DB just for reporting purpose. If purchasing a new MSSQL licence is required, he won’t be up against it.

The “General” is a open source fan and purchasing a new MSSQL licence will require some cost, and going through the process of purchasing and approval from the finance guys will take ages.

Oh well, since we’ve secured a MySQL server to store none critical data. What the hack… lets just use it as our reporting DB.

Created a batch app to exporting from MSSQL to MySQL, what this batch app do is:

  1. Selecting the data by year from MSSQL.
  2. Insert all the data to MySQL.

Next creating the DB and clone/convert table to MySQL version. Then start up the batch app and start the exporting.

At this moment, the table was created using InnoDB Engine. Picked it cause it seems to be the favorites among many “MyISAM vs InnoDB” discussion.

Server specs: 4 core 2.27 Xeon with 2GB RAM
Total Number of records: 28833017

Results: InnoDB took 5 hours++ to finish the batch app.

Investigations
It was way too long, so I look for my best friend (Google), and ask for help, found the follow post which immediately enlightened me:

  1. Choosing mySQL Database Engine: myISAM or InnoDB?
  2. Difference Between InnoDB and MyISAM
  3. Dave’s MySQL: MyISAM vs. InnoDB

Next, check with the other guys and found out that a team tried changing MyISAM to InnoDB, and later found out it was a big mistake and change back to MyISAM.

Well, clearly enough that I got my solution, changed the table’s engine to MyISAM. And fire up the batch app.

Results: 3 hours 10mins to complete the batch app.

Field Reports: Why MyISAM wins in this senario?

The batch app runs daily and import the latest data to the Reporting DB, so Insert/Update actions is key.

I wonder what magic will happen if using MyISAM on Master DB and InnoDB on the Slave DB? Well, will find out if given the chance to do this.

I’m a newbie in MySQL, so if you had spent your valuable time and read until this line, please do share your experience or your findings on MyISAM & InnoDB mixing. :)