Query EXPLAIN EXTENDED select ch1.id as id,ch1.operator as operator, ch1.lastUpdate as signInTime,ch2.lastUpdate as signOutTime, timestampdiff(second ,ch1.lastUpdate,ch2.lastUpdate) as session_time, (select count(*) from chataudit as inch where inch.eventType='OP_MESSAGE' and inch.lastUpdate > ch1.lastUpdate and inch.lastUpdate < ch2.lastUpdate and inch.operator like 'testop2' ) as total_sent_Msgs, (select count(*) from chataudit as inch where eventType='MSG_Q_SERVED' and inch.lastUpdate > ch1.lastUpdate and inch.lastUpdate < ch2.lastUpdate and inch.operator like 'testop2' ) as total_answ_Msgs from chataudit ch1 inner join chataudit as ch2 on ch1.eventType='OP_LOGIN' and ch2.eventType='OP_LOGOUT'and ch2.lastUpdate = (select min(INCH.lastUpdate) from chataudit as INCH where INCH.eventType='OP_LOGOUT' and INCH.lastUpdate > ch1.lastUpdate) where ch1.lastUpdate > '2011/01/20' and ch2.lastUpdate < '2011/01/21' and ch1.operator like 'testop2' order by ch1.lastUpdate limit 0,20, Mon Jan 24 10:40:42 2011

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYch1refchataudit_event_type,chataudit_last_updatechataudit_event_type767const9720Using where; Using filesort
1PRIMARYch2refchataudit_event_type,chataudit_last_updatechataudit_last_update8func1Using where
4DEPENDENT SUBQUERYINCHrefchataudit_event_type,chataudit_last_updatechataudit_event_type767const9928Using where
3DEPENDENT SUBQUERYinchrefchataudit_event_type,chataudit_last_updatechataudit_event_type767const742256Using where
2DEPENDENT SUBQUERYinchrefchataudit_event_type,chataudit_last_updatechataudit_event_type767const258940Using where