rendered paste bodymysql>
SELECT max(usageWhen) AS lastTime, min(usageWhen) AS firstTime, KSComputers.computerDivisionID AS divisionID, KSComputerDivisions.divisionName AS lab
FROM KSUsage
LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID
LEFT JOIN KSComputerDivisions ON KSComputers.computerDivisionID = KSComputerDivisions.divisionID
WHERE KSComputers.computerDivisionID != 'NULL' AND usageEvent = 16
GROUP BY KSComputerDivisions.divisionID
ORDER BY lab;
+---------------------+---------------------+------------+----------------------+
| lastTime | firstTime | divisionID | lab |
+---------------------+---------------------+------------+----------------------+
| 2007-06-09 16:00:24 | 2004-10-25 13:52:20 | 65525 | Crumb Macs |
| 2007-06-12 12:29:34 | 2006-02-17 10:31:40 | 65526 | Crumb Notebooks |
| 2007-06-12 14:53:50 | 2006-04-18 08:40:26 | 65533 | Crumb Reference Area |
| 2007-05-21 12:01:16 | 2005-08-29 01:18:32 | 65532 | Flagg 162 |
| 2007-06-12 14:34:04 | 2004-11-30 16:59:12 | 65530 | Kellas 100 |
| 2007-06-12 14:44:02 | 2006-05-31 10:04:37 | 65535 | Levitt Center (PC) |
| 2007-06-12 14:28:34 | 2004-08-27 11:22:02 | 65529 | Levitt Macs |
| 2007-05-21 17:31:18 | 2005-08-12 15:00:27 | 65528 | Modern Languages Lab |
| 2007-05-10 17:21:54 | 2005-03-18 08:22:00 | 65534 | Morey 114 Laptops |
| 2007-06-12 11:56:01 | 2005-03-30 12:15:02 | 65524 | Old Levitt PCs |
| 2007-06-12 06:38:09 | 2005-08-16 17:02:03 | 65531 | Podia Macs |
| 2007-06-12 12:58:32 | 2004-12-02 12:51:36 | 65523 | Satterlee 300 |
| 2007-06-12 13:17:04 | 2005-08-16 09:21:24 | 65527 | Satterlee 325 |
| 2007-06-12 12:11:56 | 2005-07-11 13:13:18 | 65522 | TEOCAT |
+---------------------+---------------------+------------+----------------------+
14 rows in set (6.36 sec)
mysql>
explain SELECT max(usageWhen) AS lastTime, min(usageWhen) AS firstTime, KSComputers.computerDivisionID AS divisionID, KSComputerDivisions.divisionName AS lab
FROM KSUsage
LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID
LEFT JOIN KSComputerDivisions ON KSComputers.computerDivisionID = KSComputerDivisions.divisionID
WHERE KSComputers.computerDivisionID != 'NULL' AND usageEvent = 16
GROUP BY KSComputerDivisions.divisionID
ORDER BY lab;
+----+-------------+---------------------+-------+------------------------------------------------------------------+-------------------------+---------+------------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+------------------------------------------------------------------+-------------------------+---------+------------------------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | KSComputers | index | PRIMARY,computer_id,computer_division_id,computer_id_division_id | computer_id_division_id | 69 | NULL | 599 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | KSComputerDivisions | ref | PRIMARY | PRIMARY | 4 | keyserver.KSComputers.computerDivisionID | 1 | |
| 1 | SIMPLE | KSUsage | ref | computer_id,event_other_time | computer_id | 65 | keyserver.KSComputers.computerID | 655 | Using where |
+----+-------------+---------------------+-------+------------------------------------------------------------------+-------------------------+---------+------------------------------------------+------+-----------------------------------------------------------+
3 rows in set (0.00 sec)