Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hi! I have a problem and no idea how to resolve it.

I'm trying to use mysql profiler.

I'm using the command:

set profiling=1;

to turn it on.

Later, i execute my query which is correlated subquery with EXISTS. It looks like:

SELECT * 

FROM A1 

WHERE EXISTS 

(SELECT B1 FROM B2 WHERE B3=A2); 

After that i use command: show profile; to see id of my query;

mysql> show profiles; 

+----------+------------+-- 

| Query_ID | Duration| Query 

+----------+------------+- 

| 1 |1224.1249092 select ... 


+----------+------------+- 

But when i'm doing query to information_schema.profiling for this query duration is much smaller. Why is such difference? Always displays 100 rows, and most of it is 'sending data'. I can't see 'starting' or 'inicialization' or 'opening tables'. When i increase limit to 200 also displays 100 rows.

mysql> select sum(duration) from information_schema.profiling where query_id=1; 

sum(duration) 
12.234170 

When i'm doing:

select count(*) from information_schema.profiling where query_id=1;

It shows:

 count(*)

'100'

But when i'm doing:

select * from information_schema.profiling where query_id=1;

SEQ starts from 1099931 and shows last 100 SEQ.

mysql> show profile for query 1; 

executing   0.000003 

Sending data    0.004343 

executing   0.000003 

Sending data    0.293625 

executing   0.000003 

Sending data    0.294627 

executing   0.000003 

Sending data    0.293227 

executing   0.000003 

Sending data    0.293953 

executing   0.000004 

Sending data    0.293879 

executing   0.000004 

Sending data    0.293932 

executing   0.000003 

Sending data    0.293652 

executing   0.000003 

Sending data    0.003999 

executing   0.000002 

Sending data    0.292655 

executing   0.000004 

Sending data    0.295063 

executing   0.000005 

Sending data    0.296534 

executing   0.000003 

Sending data    0.295801 

executing   0.000004 

Sending data    0.296010 

executing   0.000004 

Sending data    0.004082 

executing   0.000003 

Sending data    0.297623 

executing   0.000004 

Sending data    0.317437 

executing   0.000003

Sending data    0.301188 

executing   0.000004 

Sending data    0.301276 

executing   0.000004 

Sending data    0.295963 

executing   0.000004 

Sending data    0.300331 

executing   0.000004 

Sending data    0.296260 

executing   0.000003 

Sending data    0.004008 

executing   0.000002 

Sending data    0.294382 

executing   0.000004 

Sending data    0.310844 

executing   0.000005 

Sending data    0.295127 

executing   0.000006 

Sending data    0.005463 

executing   0.000003 

Sending data    0.296227 

executing   0.000003 

Sending data    0.302356 

executing   0.000004 

Sending data    0.294737 

executing   0.000004 

Sending data    0.295317 

executing   0.000004 

Sending data    0.314639 

executing   0.000003 

Sending data    0.296097 

executing   0.000005 

Sending data    0.297066 

executing   0.000003 

Sending data    0.294738 

executing   0.000003 

Sending data    0.315274 

executing   0.000004 

Sending data    0.296043 

executing   0.000003

Sending data    0.293831 

executing   0.000003 

Sending data    0.294903 

executing   0.000003 

Sending data    0.293740 

executing   0.000003 

Sending data    0.294724 

executing   0.000005 

Sending data    0.297695 

executing   0.000004 

Sending data    0.294476 

executing   0.000003 

Sending data    0.297282 

executing   0.000004 

Sending data    0.298081 

executing   0.000003 

Sending data    0.297057 

executing   0.000004 

Sending data    0.004090 

end 0.000063 

query end   0.000006 

freeing items   0.000267 

logging slow query  0.000002 

logging slow query  0.000003 

cleaning up 0.000004 

MySQL version 5.6.14

but I also try 5.5 and 5.1 and result is the same.

Any help?

Please ! I'm helpless!

Sorry for my bad English!

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.