MySQL Forums
Forum List  »  Quality Assurance

in cluster about sub query preparing time very long
Posted by: zhi zhixin
Date: November 03, 2012 02:24PM

i have a problem with in cluster env , execute very simple with the sub query example:

select subjet,sum(amount)
from cerdtl
where subjet in(select id from subjet where cmp =4)
group by subjet limit 10;


the prifile preparing time very long .... about 11 second , sometimes > 30 second , single env the same sql query time in < 100ms complete. help me..


| Status             | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary |
----------------------------------------------------------------------------------------------------------------------------------------------------
| starting            | 0.000015 | 0.000011 |  0.000004 |         0 |          0 |
| checking query cache for query | 0.000047 | 0.000046 |  0.000001 |         0 |          0 |
| Opening tables         | 0.000009 | 0.000008 |  0.000001 |         0 |          0 |
| System lock          | 0.000012 | 0.000011 |  0.000001 |         0 |          0 |
| Table lock           | 0.004555 | 0.000104 |  0.000219 |         9 |          0 |
| init              | 0.000032 | 0.000030 |  0.000002 |         0 |          0 |
| optimizing           | 0.000008 | 0.000006 |  0.000001 |         0 |          0 |
| statistics           | 0.000008 | 0.000007 |  0.000001 |         0 |          0 |
| preparing           | 0.000011 | 0.000010 |  0.000000 |         0 |          0 |
| executing           | 0.000026 | 0.000005 |  0.000021 |         0 |          0 |
| Sorting result         | 0.000003 | 0.000002 |  0.000001 |         0 |          0 |
| Sending data          | 0.002334 | 0.000046 |  0.000067 |         4 |          0 |
| optimizing           | 0.000012 | 0.000010 |  0.000001 |         0 |          0 |
| statistics           | 0.000018 | 0.000017 |  0.000001 |         0 |          0 |
| preparing           | 11.414735 | 0.186784 |  0.440432 |       14873 |         19 |
| end              | 0.000017 | 0.000011 |  0.000006 |         0 |          0 |
| query end           | 0.000013 | 0.000011 |  0.000001 |         0 |          0 |
| freeing items         | 0.000062 | 0.000026 |  0.000037 |         0 |          0 |
| storing result in query cache | 0.000008 | 0.000006 |  0.000001 |         0 |          0 |
| logging slow query       | 0.000002 | 0.000001 |  0.000001 |         0 |          0 |
| logging slow query       | 0.000003 | 0.000002 |  0.000001 |         0 |          0 |
| cleaning up          | 0.000002 | 0.000001 |  0.000000 |         0 |          0 |


explain info:

| id | select_type    | table | type      | possible_keys              | key   | key_len | ref | rows | Extra    |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY      | cerdtl | index      | NULL                   | subjet | 9    | NULL |  10 | Using where |
| 2 | DEPENDENT SUBQUERY | subjet | unique_subquery | PRIMARY,cmp_lev_fathid,cmp_fathid_id,cmp | PRIMARY | 8    | func |  1 | Using where |

Options: ReplyQuote


Subject
Views
Written By
Posted
in cluster about sub query preparing time very long
1742
November 03, 2012 02:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.