Part of Slepp's Projects — Pastebin — TURL — Imagebin — Filebin
Feedback -- English French German Japanese
Create Upload Newest Tools Donate
Sign In | Create Account

Paste Actions

Download Raw
Embed
pBox URL
Post to Twitter
Trackback (14)
Remove Paste

Paste Details

Raw format, 4 years 8 months old, and 450 hits.

Mine
Wednesday, June 13th, 2007 at 9:53:01am MDT 

  1. What would be the best way to combine the following 2 queries?
  2.  
  3. SELECT divisionName AS lab, divisionID AS id FROM KSComputerDivisions;
  4.  
  5. SELECT max(usageWhen) AS start, min(usageWhen) AS end FROM KSUsage
  6.     LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID
  7.     WHERE KSComputers.computerDivisionID = 65530 AND usageEvent = 16;
  8.  
  9. So that I would get output looking like:
  10.  
  11. id | lab | start | end |
  12.  
  13. I came up with this:
  14.  
  15. SELECT max(usageWhen) AS end, min(usageWhen) AS start, KSComputers.computerDivisionID AS id, KSComputerDivisions.divisionName AS lab
  16.     FROM KSUsage
  17.         LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID
  18.         LEFT JOIN KSComputerDivisions ON KSComputers.computerDivisionID = KSComputerDivisions.divisionID
  19.     WHERE KSComputers.computerDivisionID != 'NULL' AND (usageEvent = 15 OR usageEvent = 16)
  20.     GROUP BY KSComputers.computerDivisionID;
  21.  
  22. However it is very slow (7 seconds).
  23.  
  24. Doing seperate queries, 1 to grab the lab id's, and then a separate min/max for each lab would take approximately 3 seconds.
  25.  
  26. ###################################################################
  27. # Here is output from the queries as well as explains
  28. ###################################################################
  29.  
  30. mysql> SELECT max(usageWhen) AS end, min(usageWhen) AS start, KSComputers.computerDivisionID AS id, KSComputerDivisions.divisionName AS lab
  31.     ->     FROM KSUsage
  32.     ->         LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID
  33.     ->         LEFT JOIN KSComputerDivisions ON KSComputers.computerDivisionID = KSComputerDivisions.divisionID
  34.     ->     WHERE KSComputers.computerDivisionID != 'NULL' AND (usageEvent = 15 OR usageEvent = 16)
  35.     ->     GROUP BY KSComputers.computerDivisionID;
  36. +---------------------+---------------------+-------+----------------------+
  37. | end                 | start               | id    | lab                  |
  38. +---------------------+---------------------+-------+----------------------+
  39. | 2007-06-13 10:31:15 | 2005-07-11 09:49:16 | 65522 | TEOCAT               |
  40. | 2007-06-13 11:26:39 | 2004-12-02 12:50:51 | 65523 | Satterlee 300        |
  41. | 2007-06-12 11:56:01 | 2005-03-30 12:13:25 | 65524 | Old Levitt PCs       |
  42. | 2007-06-09 16:00:24 | 2004-10-25 13:51:31 | 65525 | Crumb Macs           |
  43. | 2007-06-12 12:29:34 | 2006-02-17 10:09:00 | 65526 | Crumb Notebooks      |
  44. | 2007-06-13 11:08:14 | 2005-08-15 19:51:45 | 65527 | Satterlee 325        |
  45. | 2007-05-21 17:31:18 | 2005-08-12 13:18:21 | 65528 | Modern Languages Lab |
  46. | 2007-06-12 16:47:58 | 2004-08-27 10:33:02 | 65529 | Levitt Macs          |
  47. | 2007-06-13 08:49:05 | 2004-11-30 16:42:36 | 65530 | Kellas 100           |
  48. | 2007-06-12 19:45:41 | 2005-08-16 16:55:22 | 65531 | Podia Macs           |
  49. | 2007-05-21 12:01:16 | 2005-08-29 01:01:19 | 65532 | Flagg 162            |
  50. | 2007-06-13 11:27:08 | 2006-04-12 10:58:07 | 65533 | Crumb Reference Area |
  51. | 2007-05-10 17:21:54 | 2005-03-18 07:55:54 | 65534 | Morey 114 Laptops    |
  52. | 2007-06-13 11:25:21 | 2006-05-31 09:47:37 | 65535 | Levitt Center (PC)   |
  53. +---------------------+---------------------+-------+----------------------+
  54. 14 rows in set (7.40 sec)
  55.  
  56. 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;
  57. +----+-------------+---------------------+-------+------------------------------------------------------+-------------------------+---------+------------------------------------------+------+-----------------------------------------------------------+
  58. | id | select_type | table               | type  | possible_keys                                        | key                     | key_len | ref                                      | rows | Extra                                                     |
  59. +----+-------------+---------------------+-------+------------------------------------------------------+-------------------------+---------+------------------------------------------+------+-----------------------------------------------------------+
  60. |  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 |
  61. |  1 | SIMPLE      | KSComputerDivisions | ref   | PRIMARY                                              | PRIMARY                 |       4 | keyserver.KSComputers.computerDivisionID |    1 |                                                           |
  62. |  1 | SIMPLE      | KSUsage             | ref   | computer_id,event_other_time,usage_event             | computer_id             |      65 | keyserver.KSComputers.computerID         |  655 | Using where                                               |
  63. +----+-------------+---------------------+-------+------------------------------------------------------+-------------------------+---------+------------------------------------------+------+-----------------------------------------------------------+
  64. 3 rows in set (0.00 sec)
  65.  
  66. mysql> SELECT divisionName AS lab, divisionID AS id FROM KSComputerDivisions;
  67. +----------------------+-------+
  68. | lab                  | id    |
  69. +----------------------+-------+
  70. | Old Levitt PCs       | 65524 |
  71. | Crumb Macs           | 65525 |
  72. | Crumb Notebooks      | 65526 |
  73. | Satterlee 325        | 65527 |
  74. | Modern Languages Lab | 65528 |
  75. | Levitt Macs          | 65529 |
  76. | Kellas 100           | 65530 |
  77. | Podia Macs           | 65531 |
  78. | Flagg 162            | 65532 |
  79. | Crumb Reference Area | 65533 |
  80. | Morey 114 Laptops    | 65534 |
  81. | Levitt Center (PC)   | 65535 |
  82. | TEOCAT               | 65522 |
  83. | Satterlee 300        | 65523 |
  84. +----------------------+-------+
  85. 14 rows in set (0.00 sec)
  86.  
  87. mysql> explain SELECT divisionName AS lab, divisionID AS id FROM KSComputerDivisions;
  88. +----+-------------+---------------------+------+---------------+------+---------+------+------+-------+
  89. | id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows | Extra |
  90. +----+-------------+---------------------+------+---------------+------+---------+------+------+-------+
  91. |  1 | SIMPLE      | KSComputerDivisions | ALL  | NULL          | NULL |    NULL | NULL |   14 |       |
  92. +----+-------------+---------------------+------+---------------+------+---------+------+------+-------+
  93. 1 row in set (0.00 sec)
  94.  
  95. mysql> SELECT max(usageWhen), min(usageWhen) FROM KSUsage     LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID     WHERE KSComputers.computerDivisionID = 65530 AND usageEvent = 16;+---------------------+---------------------+
  96. | max(usageWhen)      | min(usageWhen)      |
  97. +---------------------+---------------------+
  98. | 2007-06-13 08:49:05 | 2004-11-30 16:59:12 |
  99. +---------------------+---------------------+
  100. 1 row in set (0.35 sec)
  101.  
  102. mysql> explain SELECT max(usageWhen), min(usageWhen) FROM KSUsage     LEFT JOIN KSComputers ON KSUsage.usageComputerID = KSComputers.computerID     WHERE KSComputers.computerDivisionID = 65530 AND usageEvent = 16;
  103. +----+-------------+-------------+------+------------------------------------------------------+----------------------+---------+----------------------------------+------+-------------+
  104. | id | select_type | table       | type | possible_keys                                        | key                  | key_len | ref                              | rows | Extra       |
  105. +----+-------------+-------------+------+------------------------------------------------------+----------------------+---------+----------------------------------+------+-------------+
  106. |  1 | SIMPLE      | KSComputers | ref  | PRIMARY,computer_division_id,computer_id_division_id | computer_division_id |       5 | const                            |   23 | Using where |
  107. |  1 | SIMPLE      | KSUsage     | ref  | computer_id,event_other_time,usage_event             | computer_id          |      65 | keyserver.KSComputers.computerID |  655 | Using where |
  108. +----+-------------+-------------+------+------------------------------------------------------+----------------------+---------+----------------------------------+------+-------------+
  109. 2 rows in set (0.00 sec)
  110.  
  111. Thanks!

advertising

Update the Post

Either update this post and resubmit it with changes, or make a new post.

You may also comment on this post.

update paste below
details of the post (optional)

Note: Only the paste content is required, though the following information can be useful to others.

Save name / title?

(space separated, optional)



Please note that information posted here will expire by default in one month. If you do not want it to expire, please set the expiry time above. If it is set to expire, web search engines will not be allowed to index it prior to it expiring. Items that are not marked to expire will be indexable by search engines. Be careful with your passwords. All illegal activities will be reported and any information will be handed over to the authorities, so be good.

fantasy-obligation
fantasy-obligation