Optimization is challenge, no matter what you're doing, and unfortunately reporting in Kayako Classic is no exception. How to most effectively make the process of writing, rendering, and processing reports? Today, these questions concern a majority of the users working with reports in Kayako.
Optimizing KQL reports is a set of actions aimed at improving the speed of report rendering and reducing memory usage. To optimize your report performance, you'll need to know the right tools, and how to focus on the target result set which you want to see.
Understanding how reports work
With the KQL reports, it's important to know that it fetches data directly from the database, thus, it uses MySQL & server resources to fetch any results. So the only way to make a query run faster is to reduce the number of calculations that the software (and therefore resources) must perform. So the optimization here lies in two directions: increasing the speed of report rendering and reducing memory usage.
Optimizing KQL Reports
To increase the speed of rendering reports, it is necessary to request only needed data from large databases. That is, if for the report you need results only from a given time range, then there is no need to fetch all the data available in the database. Otherwise, a huge amount of resources will be spent on it to get all the data, not to mention the time spent. So, you’ll need some understanding of how MySQL actually makes calculations and these are the things you should check before moving on to query plans
SELECT *：Using the SELECT * statement is only recommended when you actually want to use all of the data returned by this statement. For example, if you're using a KQL query like:
SELECT * FROM 'Tickets'
This will not only fetch all the fields from the Tickets table but also put additional load on the MySQL server and add to the time required to complete this query. Instead, you should only call the required fields as shown in this example.
SELECT 'Tickets.Ticket ID', 'Tickets.Subject', 'Tickets.Department' FROM 'Tickets'
WHERE : It's always logical to use a WHERE construction in KQL. In case you do not need all the fields and records from a table, etc., but only the ones corresponding to specific conditions. This applies a positive effect on performance. So it's always suggested to add your conditions under the WHERE clause of the report like this:
SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General'
In this sample query, you're telling the Kayako to fetch results only from a specific department. Thus, the system will only look at the results under that department instead of the entire database.
You can find more useful conditions and operators in this article here: https://classic.kayako.com/article/1412-kql-condition-operator-reference
MKTIME : When filtering the report's data and minimizing the data set which needs to be queried, the time ranges play an important role. Let's say, if you're running reports for data older than a month, you can use the MKTIME function to limit the scope of your report. For example, the following report will help you limit the time range for your report and reduce it's load time:
SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General' AND 'Tickets.Creation Date'<= MKTIME(0,0,0,20,9,2017) AND 'Tickets.Creation Date'<= MKTIME(0,0,0,3,31,2017)
So this function becomes highly useful when you're running reports for large data sets. For example, you want to query all the data since 2010, you can use the MKTIME function to break into 6 reports and reduce the time taken to run this report. Just so you know, MKTIME function accepts date in the following syntax:
MKTIME(hour,minute,second,month,day,year,is_dst);
Find out more in this article here - http://php.net/manual/en/function.mktime.php
LIMIT : Again this is one more useful function to set limits to the scope of your report and avoid processing too many records. Let's say, you're fetching results from a very high volume system where thousands of records are created every day. And even after using all the conditions and filters, the report is taking too long to run, you can use the LIMIT function to break the results into parts. For example, see this query below:
SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General' AND 'Tickets.Creation Date'= Today() LIMIT 100 OFFSET 0
So this report will give you only the first 100 rows from the total results, to continue fetching the remaining records, you can increment the offset like this:
SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General' AND 'Tickets.Creation Date'= Today() LIMIT 100 OFFSET 100
This last function concludes our article and we hope that it will help you write, manage, and maintain your Kayako reports better.
Amaninder Singh