rendered paste bodyWhat would be the best way to combine the following 2 queries?
SELECT divisionName AS lab, divisionID AS id FROM KSComputerDivisions;
SELECT max(usageWhen) AS start, min(usageWhen) AS end FROM KSUsage
LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID
WHERE KSComputers.computerDivisionID = 65530 AND usageEvent = 16;
So that I would get output looking like:
id | lab | start | end |
I came up with this:
SELECT max(usageWhen) AS end, min(usageWhen) AS start, KSComputers.computerDivisionID AS id, 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 = 15 OR usageEvent = 16)
GROUP BY KSComputers.computerDivisionID;
However it is very slow (7 seconds).
Doing seperate queries, 1 to grab the lab id's, and then a separate min/max for each lab would take approximately 3 seconds.
###################################################################
# Here is output from the queries as well as explains
###################################################################
mysql> SELECT max(usageWhen) AS end, min(usageWhen) AS start, KSComputers.computerDivisionID AS id, 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 = 15 OR usageEvent = 16)
-> GROUP BY KSComputers.computerDivisionID;
+---------------------+---------------------+-------+----------------------+
| end | start | id | lab |
+---------------------+---------------------+-------+----------------------+
| 2007-06-13 10:31:15 | 2005-07-11 09:49:16 | 65522 | TEOCAT |
| 2007-06-13 11:26:39 | 2004-12-02 12:50:51 | 65523 | Satterlee 300 |
| 2007-06-12 11:56:01 | 2005-03-30 12:13:25 | 65524 | Old Levitt PCs |
| 2007-06-09 16:00:24 | 2004-10-25 13:51:31 | 65525 | Crumb Macs |
| 2007-06-12 12:29:34 | 2006-02-17 10:09:00 | 65526 | Crumb Notebooks |
| 2007-06-13 11:08:14 | 2005-08-15 19:51:45 | 65527 | Satterlee 325 |
| 2007-05-21 17:31:18 | 2005-08-12 13:18:21 | 65528 | Modern Languages Lab |
| 2007-06-12 16:47:58 | 2004-08-27 10:33:02 | 65529 | Levitt Macs |
| 2007-06-13 08:49:05 | 2004-11-30 16:42:36 | 65530 | Kellas 100 |
| 2007-06-12 19:45:41 | 2005-08-16 16:55:22 | 65531 | Podia Macs |
| 2007-05-21 12:01:16 | 2005-08-29 01:01:19 | 65532 | Flagg 162 |
| 2007-06-13 11:27:08 | 2006-04-12 10:58:07 | 65533 | Crumb Reference Area |
| 2007-05-10 17:21:54 | 2005-03-18 07:55:54 | 65534 | Morey 114 Laptops |
| 2007-06-13 11:25:21 | 2006-05-31 09:47:37 | 65535 | Levitt Center (PC) |
+---------------------+---------------------+-------+----------------------+
14 rows in set (7.40 sec)
mysql> explain SELECT max(usageWhen) AS end, min(usageWhen) AS start, KSComputers.computerDivisionID AS id, 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 = 15 OR usageEvent = 16) GROUP BY KSComputers.computerDivisionID;
+----+-------------+---------------------+-------+------------------------------------------------------+-------------------------+---------+------------------------------------------+------+-----------------------------------------------------------+
| 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> SELECT divisionName AS lab, divisionID AS id FROM KSComputerDivisions;
+----------------------+-------+
| lab | id |
+----------------------+-------+
| Old Levitt PCs | 65524 |
| Crumb Macs | 65525 |
| Crumb Notebooks | 65526 |
| Satterlee 325 | 65527 |
| Modern Languages Lab | 65528 |
| Levitt Macs | 65529 |
| Kellas 100 | 65530 |
| Podia Macs | 65531 |
| Flagg 162 | 65532 |
| Crumb Reference Area | 65533 |
| Morey 114 Laptops | 65534 |
| Levitt Center (PC) | 65535 |
| TEOCAT | 65522 |
| Satterlee 300 | 65523 |
+----------------------+-------+
14 rows in set (0.00 sec)
mysql> explain SELECT divisionName AS lab, divisionID AS id FROM KSComputerDivisions;
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | KSComputerDivisions | ALL | NULL | NULL | NULL | NULL | 14 | |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> SELECT max(usageWhen), min(usageWhen) FROM KSUsage LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID WHERE KSComputers.computerDivisionID = 65530 AND usageEvent = 16;+---------------------+---------------------+
| max(usageWhen) | min(usageWhen) |
+---------------------+---------------------+
| 2007-06-13 08:49:05 | 2004-11-30 16:59:12 |
+---------------------+---------------------+
1 row in set (0.35 sec)
mysql> explain SELECT max(usageWhen), min(usageWhen) FROM KSUsage LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID WHERE KSComputers.computerDivisionID = 65530 AND usageEvent = 16;
+----+-------------+-------------+------+------------------------------------------------------+----------------------+---------+----------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+------------------------------------------------------+----------------------+---------+----------------------------------+------+-------------+
| 1 | SIMPLE | KSComputers | ref | PRIMARY,computer_division_id,computer_id_division_id | computer_division_id | 5 | const | 23 | Using where |
| 1 | SIMPLE | KSUsage | ref | computer_id,event_other_time,usage_event | computer_id | 65 | keyserver.KSComputers.computerID | 655 | Using where |
+----+-------------+-------------+------+------------------------------------------------------+----------------------+---------+----------------------------------+------+-------------+
2 rows in set (0.00 sec)
Thanks!