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
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 English!