Query hangs, HELP!

[chaos83666]

Hey guys... I've tried google and reference material, but without knowing what direction to look I'm screwed. I have a query that simply hangs until it times out. I'm sure I'm doing something stupidly wrong, but I have no idea what it is. Could someone at least point me in the right direction? Query below:

 

select

month(DateTime) as m, year(DateTime) as y,

(select count(*) from Transaction inner join Device on Device.MAC_Address = Transaction.MAC_Address where DiagCode in ('52','53','54') and month(DateTime) = m and year(DateTime) = y) as fanCount,

(select count(*) from Transaction inner join Device on Device.MAC_Address = Transaction.MAC_Address where DiagCode in ('56','57','58') and month(DateTime) = m and year(DateTime) = y) as humCount,

(select count(*) from Transaction inner join Device on Device.MAC_Address = Transaction.MAC_Address where DiagCode = '59' and month(DateTime) = m and year(DateTime) = y) as scent1Count,

(select count(*) from Transaction inner join Device on Device.MAC_Address = Transaction.MAC_Address where DiagCode = '60' and month(DateTime) = m and year(DateTime) = y) as scent2Count,

(select count(*) from Transaction inner join Device on Device.MAC_Address = Transaction.MAC_Address where DiagCode = '61' and month(DateTime) = m and year(DateTime) = y) as scent3Count,

(select count(*) from Transaction inner join Device on Device.MAC_Address = Transaction.MAC_Address where DiagCode = '62' and month(DateTime) = m and year(DateTime) = y) as scent4Count,

(select count(*) from Transaction inner join Device on Device.MAC_Address = Transaction.MAC_Address where DiagCode = '63' and month(DateTime) = m and year(DateTime) = y) as scent5Count

from Transaction group by month(DateTime), year(DateTime);

[fenway]

I'm guessing you don't have any indexes.

 

Post EXPLAIN output.

[chaos83666]

'1', 'PRIMARY', 'Transaction', 'ALL', NULL, NULL, NULL, NULL, '74370', 'Using temporary; Using filesort'

'8', 'DEPENDENT SUBQUERY', 'Device', 'index', 'idxMAC', 'idxMAC', '19', NULL, '17', 'Using index'

'8', 'DEPENDENT SUBQUERY', 'Transaction', 'ref', 'idxDiagCodeMAC', 'idxDiagCodeMAC', '21', 'hotelairspa.Device.MAC_Address,const', '112', 'Using where'

'7', 'DEPENDENT SUBQUERY', 'Device', 'index', 'idxMAC', 'idxMAC', '19', NULL, '17', 'Using index'

'7', 'DEPENDENT SUBQUERY', 'Transaction', 'ref', 'idxDiagCodeMAC', 'idxDiagCodeMAC', '21', 'hotelairspa.Device.MAC_Address,const', '112', 'Using where'

'6', 'DEPENDENT SUBQUERY', 'Device', 'index', 'idxMAC', 'idxMAC', '19', NULL, '17', 'Using index'

'6', 'DEPENDENT SUBQUERY', 'Transaction', 'ref', 'idxDiagCodeMAC', 'idxDiagCodeMAC', '21', 'hotelairspa.Device.MAC_Address,const', '112', 'Using where'

'5', 'DEPENDENT SUBQUERY', 'Device', 'index', 'idxMAC', 'idxMAC', '19', NULL, '17', 'Using index'

'5', 'DEPENDENT SUBQUERY', 'Transaction', 'ref', 'idxDiagCodeMAC', 'idxDiagCodeMAC', '21', 'hotelairspa.Device.MAC_Address,const', '112', 'Using where'

'4', 'DEPENDENT SUBQUERY', 'Device', 'index', 'idxMAC', 'idxMAC', '19', NULL, '17', 'Using index'

'4', 'DEPENDENT SUBQUERY', 'Transaction', 'ref', 'idxDiagCodeMAC', 'idxDiagCodeMAC', '21', 'hotelairspa.Device.MAC_Address,const', '112', 'Using where'

'3', 'DEPENDENT SUBQUERY', 'Device', 'index', 'idxMAC', 'idxMAC', '19', NULL, '17', 'Using index'

'3', 'DEPENDENT SUBQUERY', 'Transaction', 'ref', 'idxDiagCodeMAC', 'idxDiagCodeMAC', '19', 'hotelairspa.Device.MAC_Address', '524', 'Using where'

'2', 'DEPENDENT SUBQUERY', 'Device', 'index', 'idxMAC', 'idxMAC', '19', NULL, '17', 'Using index'

'2', 'DEPENDENT SUBQUERY', 'Transaction', 'ref', 'idxDiagCodeMAC', 'idxDiagCodeMAC', '19', 'hotelairspa.Device.MAC_Address', '524', 'Using where'

 

 

I was trying to get the explain output for you but it was still hung. I had to restart mysql to be able to run explain. Sorry man.

[DavidAM]

I think that, instead of doing so many sub-queries, I would do something along these lines:

 

select month(DateTime) as m, year(DateTime) as y,
SUM(IF DiagCode in ('52','53','54') THEN 1 ELSE 0 END) AS fanCount,
SUM(IF DiagCode in ('56','57','58') THEN 1 ELSE 0 END) as humCount,
...
from Transaction join Device on Device.MAC_Address = Transaction.MAC_Address 
group by month(DateTime), year(DateTime);

 

Be aware that DateTime and Transaction are reserved words in mySql and will likely give you problems trying to use them in queries. I would consider renaming these columns/tables.

 

[chaos83666]

I will give that a try. Unfortunately I inherited this db with those names. If I can afford the time I do plan to rename them.

[DavidAM]

Oops, I started with a CASE statement, the syntax for IF would be:

 

select month(DateTime) as m, year(DateTime) as y,
SUM(IF DiagCode in ('52','53','54'), 1, 0) AS fanCount,
SUM(IF DiagCode in ('56','57','58'), 1, 0) as humCount,
...
from Transaction join Device on Device.MAC_Address = Transaction.MAC_Address 
group by month(DateTime), year(DateTime);

 

If you start having problems with the column names, you can use back-ticks to quote them:

 

SELECT `DateTime` ...

[fenway]

Sorting 70K rows isn't great -- but yes, removing the subqueries will help.