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.

ASP.NET MVC vs Yii MVC

yii frameworkI wanted to create a simple web application that allows me manage the data in MySQL Database, I do not wish to waste my time on coding simple CRUD functions. I prefer to generate the codes every time i modified the database.

Been notice that ASP.NET MVC would help me to achieve that, able to find samples of running ASP.NET MVC with MySQL (By default, the codes run with MsSQL).  But in the end, i found out few issues of using it.

  1. All Visual Studio Express version does not allow 3rd party extensibility.
    Means I can’t use Entity Framework with .Net Connector by MySQL to auto generate the “Model” with Visual Studio.
    Not everyone have a Standard(Paid licence) version of Visual Studio, I’m using Visual Studio 2008 Standard for the testing above.
  2. Since I’m using Visual Studio 2008 Standard, I’m only able to use ASP.NET MVC2, which now they have launched ASP.NET MVC3 (with better features and support).
So I have to look for other alternatives, but somehow, “Yii” has been a buzzword among the company. There are even presentation and demo on how to create sites in 10mins or so.
Well, is free and there is so many Gurus around to guide me and give me crash course and all I need to do is to learn how to setup Apache & PHP on my Windows. Why not give it a try… after all, if it is PHP, there shoudn’t be a problem generating CRUD and models with MySQL.
After the completed the setup, I start to follow the quick guide on setting up Yii and guide to setup my first Yii Application. Encountered the problems bellow which took some of my time to diagnose:
  • Warning: date(): It is not safe to rely on the system’s timezone settings
    “WTF”
    was the first thing that comes on my mind, then I goggled and found out Setting timezone in your php.ini date.timezone = “UTC” to fix this.
  • CDbConnection failed to open the DB connection: could not find driver
    Again, “WTF” comes in my mind again.

    1. First, I checked if my DB connection setting was correct for Yii, and it looks good.
    2. Next, lots of google results showing “extension=php_mysql.dll was not enabled“. So I enabled it in PHP.ini, and restart the apache. But I still can’t connect my first Yii application.
    3. Dafaq~~~~ done more google and found a new word call “PDO extenstion”. So I run my personal favorite PHP code (sorry, not “hello world”), phpinfo.php page and looked for the PDO section, nothing was loaded.
      Checked with all the Yii Gurus but non of them encounter this issue, reason is they install Apache & PHP via Xampp and Wamp… :(
    4. After sometime google-ing, found out that I need to do the following chances.
      php.ini set extension_dir = “C:\webserver\php\ext”
Damn~~~~ *@#*&*&%*#&*!&$*&!@($& !!!!
And finally, my first Yii Application runs~~~ Horaay….
So the lesson learn in this experience is, all those tutorial or videos of “Creating Yii application in 10min” is for people who knows and been using PHP.
For a .NET guy like me, we’ll need more time than 10 mins to run our “First” Yii application, because we learn how to setup the server first…. So is 20 mins for to really create our “First” Yii application (if we find a video or tutorial on “setting up apache & php in 10 mins“)
Overall, Yii serve the purpose of letting me do more advance stuff instead of wasting time creating simple and boring management page for internal staff.
Next… The UI control challenge awaits me…

 

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. :)