Need to optimize MySQL query

Question

I have a query that takes 33 seconds, is there a better way to rewrite it, how can I convert it to a procedure

select ut.templateId, ( case when ut.reportTypeId=4 then 'Account' when ut.reportTypeId=5 then 'Campaign' when ut.reportTypeId=6 then 'AdGroup' when ut.reportTypeId=7 then 'Ad' when ut.reportTypeId=8 then 'Keyword' end )as ReportType , ur.reportId, (case when timestampdiff(SECOND,b.createdTS,a.createdTS) < 5 then a.reportId else 0 end) as '<5secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 5 and 10 then a.reportId else 0 end) as '5-10secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 11 and 20 then a.reportId else 0 end) as '11-20secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 21 and 30 then a.reportId else 0 end) as '21-30secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 31 and 60 then a.reportId else 0 end) as '31-60secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 61 and 120 then a.reportId else 0 end) as '61-120secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 121 and 1800 then a.reportId else 0 end) as '2-30mins', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) > 1800 then a.reportId else 0 end) as '>30mins' from (select reportId,createdTS from T_ReportMonitor where status='EndSP')a, (select reportId,createdTS from T_ReportMonitor where status='BeginSP')b, (select templateId,reportTypeId,reportConsoleType from T_UserTemplate) ut, (select reportId,templateId,createdTS,modifiedTS,isDeleted from T_UserReport) ur where a.reportId=b.reportId and date(ur.createdTS) = 20120731 and ut.templateId=ur.templateId and reportConsoleType in ('Adser','APIAdser') and ur.isDeleted=false and a.reportId=ur.reportId and ur.reportId!=313509 AND ur.reportId!=313510 AND ur.reportId!=313511 AND ur.reportId!=313512 AND ur.reportId!=313509 AND ur.reportId!=313510 AND ur.reportId!=313511 AND ur.reportId!=313512 AND ur.reportId!=313520; 

Explanation Query result:

 +----+-------------+-----------------+------+---------------+------+---------+------+--------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+------+---------------+------+---------+------+--------+--------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 20071 | | | 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 20072 | Using where; Using join buffer | | 1 | PRIMARY | <derived5> | ALL | NULL | NULL | NULL | NULL | 148591 | Using where; Using join buffer | | 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 154030 | Using where; Using join buffer | | 5 | DERIVED | T_UserReport | ALL | NULL | NULL | NULL | NULL | 124008 | | | 4 | DERIVED | T_UserTemplate | ALL | NULL | NULL | NULL | NULL | 151745 | | | 3 | DERIVED | T_ReportMonitor | ALL | NULL | NULL | NULL | NULL | 60849 | Using where | | 2 | DERIVED | T_ReportMonitor | ALL | NULL | NULL | NULL | NULL | 60849 | Using where | +----+-------------+-----------------+------+---------------+------+---------+------+--------+--------------------------------+ 

I have keys in the columns that are used in the where clause and any other comparison, but none of them are used in the query, due to the reason they are a derived query.

Answer 1

The main problem with your request is the use of subqueries. MySQL cannot use the index in a subquery, since it basically creates a new table in memory (or on disk) for your subquery. Try making connections instead.

Try this one

 select ut.templateId, ( case when ut.reportTypeId=4 then 'Account' when ut.reportTypeId=5 then 'Campaign' when ut.reportTypeId=6 then 'AdGroup' when ut.reportTypeId=7 then 'Ad' when ut.reportTypeId=8 then 'Keyword' end )as ReportType , ur.reportId, (case when timestampdiff(SECOND,b.createdTS,a.createdTS) < 5 then a.reportId else 0 end) as '<5secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 5 and 10 then a.reportId else 0 end) as '5-10secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 11 and 20 then a.reportId else 0 end) as '11-20secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 21 and 30 then a.reportId else 0 end) as '21-30secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 31 and 60 then a.reportId else 0 end) as '31-60secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 61 and 120 then a.reportId else 0 end) as '61-120secs', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 121 and 1800 then a.reportId else 0 end) as '2-30mins', (case when timestampdiff(SECOND,b.createdTS,a.createdTS) > 1800 then a.reportId else 0 end) as '>30mins' from T_ReportMonitor as a JOIN T_ReportMonitor as b ON (a.reportId=b.reportId) JOIN T_UserReport as ur ON (a.reportId=ur.reportId) JOIN T_UserTemplate as ut ON (ut.templateId=ur.templateId) WHERE a.status='EndSP' AND b.status='BeginSP' and date(ur.createdTS) = 20120731 and reportConsoleType in ('Adser','APIAdser') and ur.isDeleted=false and ur.reportId NOT IN (313509,313510,313511,313512,313509,313510,313511,313512,313520); 

Make sure you have the key T_ReportMonitor.reportId, T_ReportMonitor.status and T_UserReport.reportId.

There is one more thing that will worsen your request. You use the function in where:

 date(ur.createdTS) 

This means that MySQL will process each row to find out what the result of this function will be. This may even be the biggest increase in productivity. Try either making this field a date field (or creating a new date field), or using something like

 WHERE ur.createdTS>='2012-07-31 00:00:00' AND ur.createdTS<='2012-07-31 23:95:59' 

Answer 2

They are a derivative request, but I think there is no reason for them.

For example, note that the reportId on each line is always the same. Then it is always useful to refer to the reportId the driving table (MySQL should be smart enough to do it yourself).

For example, tables a and b can be combined in this way.

 FROM T_UserReport AS ur JOIN T_ReportMonitor AS a ON (a.reportId = ur.reportId AND a.status = 'EndSP') JOIN T_ReportMonitor AS b ON (b.reportId = ur.reportId AND b.status = 'BeginSP') 

and T_ReportMonitor then only an index on status and reportId :

 CREATE INDEX ut_ndx ON T_ReportMonitor ( status, reportId, createdTS ) 

This allows MySQL to immediately select EndSP entries for a and have a reportId column for JOIN; Having done this, he also discovers himself with the created TS for the request. The data table itself (much more) should never be available at all.

The same applies to other tables. If you

• JOIN column1,
• has a simple WHERE filter on column2 [AND column2a ...] clause values ,
• only column 3 is needed in the request body
• and the table is significantly larger than the three columns named so far,

then it will be profitable for you to do

JOIN table AS asias ON (alias.column1 = ... AND alias.column2 = 'filter value)

and have an index such as

 CREATE INDEX table_ndx ON table ( column2, column1, column3 )