rendered paste bodymysql>
SELECT 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;
+------------+----------------------+
| divisionID | lab |
+------------+----------------------+
| 65525 | Crumb Macs |
| 65526 | Crumb Notebooks |
| 65533 | Crumb Reference Area |
| 65532 | Flagg 162 |
| 65530 | Kellas 100 |
| 65535 | Levitt Center (PC) |
| 65529 | Levitt Macs |
| 65528 | Modern Languages Lab |
| 65534 | Morey 114 Laptops |
| 65524 | Old Levitt PCs |
| 65531 | Podia Macs |
| 65523 | Satterlee 300 |
| 65527 | Satterlee 325 |
| 65522 | TEOCAT |
+------------+----------------------+
14 rows in set (6.18 sec)
mysql>
explain SELECT 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_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,usage_event | 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)