Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jul 15th, 2012  |  syntax: None  |  size: 12.30 KB  |  hits: 22  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Same query, same system, different execution time
  2. SELECT
  3.     f.form_question_has_answer_id
  4. FROM
  5.     form_question_has_answer f
  6. INNER JOIN
  7.     project_company_has_user p ON f.form_question_has_answer_user_id = p.project_company_has_user_user_id
  8. INNER JOIN
  9.     company c ON p.project_company_has_user_company_id = c.company_id
  10. INNER JOIN
  11.     project p2 ON p.project_company_has_user_project_id = p2.project_id
  12. INNER JOIN
  13.     user u ON p.project_company_has_user_user_id = u.user_id
  14. INNER JOIN
  15.     form f2 ON p.project_company_has_user_project_id = f2.form_project_id
  16. WHERE
  17.     (f2.form_template_name = 'custom' AND p.project_company_has_user_garbage_collection = 0 AND p.project_company_has_user_project_id = '29') AND (LCASE(c.company_country) LIKE '%ge%' OR LCASE(c.company_country) LIKE '%abcde%') AND f.form_question_has_answer_form_id = '174'
  18.        
  19. +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+
  20. | id | select_type | table | type   | possible_keys                                                                                                                                | key                              | key_len | ref                                                | rows | Extra       |
  21. +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+
  22. |  1 | SIMPLE      | p2    | const  | PRIMARY                                                                                                                                      | PRIMARY                          | 4       | const                                              |    1 | Using index |
  23. |  1 | SIMPLE      | f     | ref    | form_question_has_answer_form_id,form_question_has_answer_user_id                                                                            | form_question_has_answer_form_id | 4       | const                                              |  796 | Using where |
  24. |  1 | SIMPLE      | u     | eq_ref | PRIMARY                                                                                                                                      | PRIMARY                          | 4       | new_klarents.f.form_question_has_answer_user_id    |    1 | Using index |
  25. |  1 | SIMPLE      | p     | ref    | project_company_has_user_unique_key,project_company_has_user_user_id,project_company_has_user_company_id,project_company_has_user_project_id | project_company_has_user_user_id | 4       | new_klarents.f.form_question_has_answer_user_id    |    1 | Using where |
  26. |  1 | SIMPLE      | f2    | ref    | form_project_id                                                                                                                              | form_project_id                  | 4       | const                                              |   15 | Using where |
  27. |  1 | SIMPLE      | c     | eq_ref | PRIMARY                                                                                                                                      | PRIMARY                          | 4       | new_klarents.p.project_company_has_user_company_id |    1 | Using where |
  28. +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+
  29.        
  30. SELECT
  31.     COUNT(*) AS num_results
  32. FROM (SELECT
  33.         f.form_question_has_answer_id
  34.     FROM
  35.         form_question_has_answer f
  36.     INNER JOIN
  37.         project_company_has_user p ON f.form_question_has_answer_user_id = p.project_company_has_user_user_id
  38.     INNER JOIN
  39.         company c ON p.project_company_has_user_company_id = c.company_id
  40.     INNER JOIN
  41.         project p2 ON p.project_company_has_user_project_id = p2.project_id
  42.     INNER JOIN
  43.         user u ON p.project_company_has_user_user_id = u.user_id
  44.     INNER JOIN
  45.         form f2 ON p.project_company_has_user_project_id = f2.form_project_id
  46.     WHERE
  47.         (f2.form_template_name = 'custom' AND p.project_company_has_user_garbage_collection = 0 AND p.project_company_has_user_project_id = '29') AND (LCASE(c.company_country) LIKE '%ge%' OR LCASE(c.company_country) LIKE '%abcde%') AND f.form_question_has_answer_form_id = '174'
  48.     GROUP BY
  49.         f.form_question_has_answer_id;) dctrn_count_query;
  50.        
  51. +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+------------------------------+
  52.     | id | select_type | table | type   | possible_keys                                                                                                                                                                            | key                              | key_len | ref                                                | rows | Extra                        |
  53.     +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+------------------------------+
  54.     |  1 | PRIMARY     | NULL  | NULL   | NULL                                                                                                                                                                                     | NULL                             | NULL    | NULL                                               | NULL | Select tables optimized away |
  55.     |  2 | DERIVED     | p2    | const  | PRIMARY                                                                                                                                                                                  | PRIMARY                          | 4       |                                                    |    1 | Using index                  |
  56.     |  2 | DERIVED     | f     | ref    | form_question_has_answer_form_id,form_question_has_answer_user_id                                                                                                                        | form_question_has_answer_form_id | 4       |                                                    |  797 | Using where                  |
  57.     |  2 | DERIVED     | p     | ref    | project_company_has_user_unique_key,project_company_has_user_user_id,project_company_has_user_company_id,project_company_has_user_project_id,project_company_has_user_garbage_collection | project_company_has_user_user_id | 4       | new_klarents.f.form_question_has_answer_user_id    |    1 | Using where                  |
  58.     |  2 | DERIVED     | f2    | ref    | form_project_id                                                                                                                                                                          | form_project_id                  | 4       |                                                    |   15 | Using where                  |
  59.     |  2 | DERIVED     | c     | eq_ref | PRIMARY                                                                                                                                                                                  | PRIMARY                          | 4       | new_klarents.p.project_company_has_user_company_id |    1 | Using where                  |
  60.     |  2 | DERIVED     | u     | eq_ref | PRIMARY                                                                                                                                                                                  | PRIMARY                          | 4       | new_klarents.p.project_company_has_user_user_id    |    1 | Using where; Using index     |
  61.     +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+------------------------------+
  62.        
  63. +-------------+
  64. | num_results |
  65. +-------------+
  66. |           3 |
  67. +-------------+
  68. 1 row in set (2 min 14.28 sec)
  69.        
  70. +--------------------------------+------------+
  71. | Status                         | Duration   |
  72. +--------------------------------+------------+
  73. | starting                       |   0.000016 |
  74. | checking query cache for query |   0.000057 |
  75. | Opening tables                 |   0.004388 |
  76. | System lock                    |   0.000003 |
  77. | Table lock                     |   0.000036 |
  78. | init                           |   0.000030 |
  79. | optimizing                     |   0.000016 |
  80. | statistics                     |   0.000111 |
  81. | preparing                      |   0.000022 |
  82. | executing                      |   0.000004 |
  83. | Sorting result                 |   0.000002 |
  84. | Sending data                   | 136.213836 |
  85. | end                            |   0.000007 |
  86. | query end                      |   0.000002 |
  87. | freeing items                  |   0.004273 |
  88. | storing result in query cache  |   0.000010 |
  89. | logging slow query             |   0.000001 |
  90. | logging slow query             |   0.000002 |
  91. | cleaning up                    |   0.000002 |
  92. +--------------------------------+------------+
  93.        
  94. top - 15:43:25 up 78 days, 12:11,  4 users,  load average: 1.42, 0.99, 0.78
  95. Tasks: 162 total,   2 running, 160 sleeping,   0 stopped,   0 zombie
  96. Cpu(s):  0.1%us, 50.4%sy,  0.0%ni, 49.5%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
  97. Mem:   4037868k total,  3772580k used,   265288k free,   243704k buffers
  98. Swap:  3905528k total,   265384k used,  3640144k free,  1207944k cached
  99.  
  100. top - 15:44:31 up 78 days, 12:13,  4 users,  load average: 1.94, 1.23, 0.87
  101. Tasks: 160 total,   2 running, 157 sleeping,   0 stopped,   1 zombie
  102. Cpu(s):  0.2%us, 50.6%sy,  0.0%ni, 49.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
  103. Mem:   4037868k total,  3834300k used,   203568k free,   243736k buffers
  104. Swap:  3905528k total,   265384k used,  3640144k free,  1207804k cached
  105.        
  106. top - 11:04:58 up 79 days,  7:33,  4 users,  load average: 0.39, 0.58, 0.76
  107. Tasks: 156 total,   1 running, 155 sleeping,   0 stopped,   0 zombie
  108. Cpu(s):  3.3%us,  2.8%sy,  0.0%ni, 93.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
  109. Mem:   4037868k total,  3676136k used,   361732k free,   271480k buffers
  110. Swap:  3905528k total,   268736k used,  3636792k free,  1063432k cached
  111.        
  112. top - 15:47:07 up 110 days, 22:11,  7 users,  load average: 0.17, 0.07, 0.06
  113. Tasks: 210 total,   2 running, 208 sleeping,   0 stopped,   0 zombie
  114. Cpu(s):  0.1%us,  0.2%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
  115. Mem:   4111972k total,  1821100k used,  2290872k free,   238860k buffers
  116. Swap:  4183036k total,    66472k used,  4116564k free,   921072k cached
  117.        
  118. DESCRIBE SELECT
  119.     COUNT(*) AS num_results
  120. FROM (SELECT
  121.         f.form_question_has_answer_id
  122.     FROM
  123.         form_question_has_answer f
  124.     INNER JOIN
  125.         project_company_has_user p ON f.form_question_has_answer_user_id = p.project_company_has_user_user_id
  126.     INNER JOIN
  127.         company c ON p.project_company_has_user_company_id = c.company_id
  128.     INNER JOIN
  129.         project p2 ON p.project_company_has_user_project_id = p2.project_id
  130.     INNER JOIN
  131.         user u ON p.project_company_has_user_user_id = u.user_id
  132.     INNER JOIN
  133.         form f2 ON p.project_company_has_user_project_id = f2.form_project_id
  134.     WHERE
  135.         (f2.form_template_name = 'custom' AND p.project_company_has_user_garbage_collection = 0 AND p.project_company_has_user_project_id = '29') AND (LCASE(c.company_country) LIKE '%finland%' OR LCASE(c.company_country) LIKE '%finnland%') AND f.form_question_has_answer_form_id = '174'
  136.     GROUP BY
  137.         f.form_question_has_answer_id;) dctrn_count_query;
create a new version of this paste RAW Paste Data