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.
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.
Ulasan
Catat Ulasan