Friday, November 22, 2013
uih. rasanya dah lama tak update blog ni. sekarang ni tengah sibuk mengejar dateline. "rebranding" ashadee.com. Ok. update kali ni taknak post benda2 teknikal.. nak santai sikit. emmm. apa nak tulis ek? <-- ( orang johor mesti ada perkataan "ek" nih ). nanti lah buat post baru. cukup la quota bulan ni hahaha.
Thursday, October 24, 2013
Tuesday, October 22, 2013
I had this problem in one of my project. Currently, we have more than 1 million data per user especially for the log file table. This huge data affecting our stats reporting and become slow and take too long to load the stats.
Here is what I found during optimisation process.
We start with normal query as below. Both query do the same objective, just want to see if there is any performance impact when I use MONTH and YEAR function in WHERE clause compared to BETWEEN … AND … for the DATETIME selection.
From the result, there is no big deal with the both method.
We continue with the test.
See?. There is huge improvement when I convert link_id to 32-bit unsigned value when do GROUP BY. The test not end here, I change my link_id field type from INT to UNSIGNED INT and test to see is there any different. Below is the test result.
huh? there is not much different after changing field type from INT to UNSIGNED INT ?.
so the conclusion here, converting GROUP BY to 32-bit unsigned does affect query performance.