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)
mysql> show index from KSUsage;
+---------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| KSUsage | 0 | PRIMARY | 1 | usageID | A | 1493033 | NULL | NULL | | BTREE | |
| KSUsage | 1 | computer_id | 1 | usageComputerID | A | 2279 | NULL | NULL | YES | BTREE | |
| KSUsage | 1 | usage_when | 1 | usageWhen | A | 1493033 | NULL | NULL | YES | BTREE | |
| KSUsage | 1 | usage_other_time | 1 | usageOtherTime | A | 1493033 | NULL | NULL | YES | BTREE | |
| KSUsage | 1 | event_other_time | 1 | usageEvent | A | 13 | NULL | NULL | YES | BTREE | |
| KSUsage | 1 | event_other_time | 2 | usageOtherTime | A | 1493033 | NULL | NULL | YES | BTREE | |
+---------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)
mysql> show index from KSComputers;
+-------------+------------+-------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| KSComputers | 0 | PRIMARY | 1 | computerID | A | NULL | NULL | NULL | | BTREE | |
| KSComputers | 0 | PRIMARY | 2 | computerServerID | A | 599 | NULL | NULL | | BTREE | |
| KSComputers | 1 | computer_id | 1 | computerID | A | 599 | NULL | NULL | | BTREE | |
| KSComputers | 1 | computer_division_id | 1 | computerDivisionID | A | 14 | NULL | NULL | YES | BTREE | |
| KSComputers | 1 | computer_id_division_id | 1 | computerID | A | 599 | NULL | NULL | | BTREE | |
| KSComputers | 1 | computer_id_division_id | 2 | computerDivisionID | A | 599 | NULL | NULL | YES | BTREE | |
+-------------+------------+-------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)
mysql> show index from KSComputerDivisions;
+---------------------+------------+---------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+---------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| KSComputerDivisions | 0 | PRIMARY | 1 | divisionID | A | NULL | NULL | NULL | | BTREE | |
| KSComputerDivisions | 0 | PRIMARY | 2 | divisionServerID | A | 14 | NULL | NULL | | BTREE | |
| KSComputerDivisions | 1 | division_name | 1 | divisionName | A | NULL | NULL | NULL | YES | BTREE | |
+---------------------+------------+---------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)