After SET PROFILING=1, I ran a multiple joins query (multiple left joins, and inside every left join is a subquery from several same tables). See below the time spent in every status after running twice of this query:
mysql> SHOW PROFILE SOURCE FOR QUERY 6;
+--------------------------------+----------+-----------------------+--------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+--------------------------------+----------+-----------------------+--------------+-------------+
| starting | 0.000046 | NULL | NULL | NULL |
| Waiting for query cache lock | 0.000010 | try_lock | sql_cache.cc | 454 |
| checking query cache for query | 0.000035 | send_result_to_client | sql_cache.cc | 1561 |
| checking privileges on cached | 0.000009 | send_result_to_client | sql_cache.cc | 1652 |
| checking permissions | 0.000012 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000009 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000008 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000009 | check_access | sql_parse.cc | 4751 |
| sending cached result to clien | 0.000094 | send_result_to_client | sql_cache.cc | 1749 |
| logging slow query | 0.000009 | log_slow_statement | sql_parse.cc | 1461 |
| cleaning up | 0.000010 | dispatch_command | sql_parse.cc | 1417 |
+--------------------------------+----------+-----------------------+--------------+-------------+
31 rows in set (0.00 sec)
I wonder why I have got so many 'checking permissions'. There is no problem with the query result, the basic structure of the query is:
select field1, field2, etc
from A
inner join B on (A.id = B.bid)
inner join C on (...)
...
left join
(select some fields
from A
inner join B on (A.id = B.bid)
inner join C on (...)
...
where ...
group by ...) as tbl_new1 on (...)
left join
(select some fields
from A
inner join B on (A.id = B.bid)
inner join C on (...)
...
where ...
group by ...) as tbl_new2 on (...)
left join
(select some fields
from A
inner join B on (A.id = B.bid)
inner join C on (...)
...
where ...
group by ...) as tbl_new3 on (...)
where ...
GROUP BY ... ;
Thanks.
