CODEWITHSUNDEEP
mysql
edited
edited

Reputation: 73

Re-create Code for SHOW PROFILES variable in MySQL

Hi i am using MySQL,

SHOW PROFILES;

Returns a long list, with rows like this for example:

 |Query_ID|Duration     |Query
>| 9    |    0.00028550 |   INSERT INTO Duration(Status,DURATION,Query_ID)|

I am trying to replicate this, so i can insert the results into a table and add a WHERE clause.

So i tried this 

SELECT Query_ID, STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 9;

But this returns a lower level view(Which i do not want):

starting 0.000053 Opening tables 0.000088

Which is the same as:

SHOW PROFILE;

How would i recreate SHOW PROFILES? So i can perform something like this:

INSERT INTO Duration(Query_ID,Status,DURATION)
SELECT * FROM SHOW PROFILE
WHERE PROFILE.QUERY_ID = 9;

Thanks

Upvotes: 0

Views: 297

Answers (1)

eggyal
eggyal

Reputation: 125865

Group by QUERY_ID. To obtain the status, you want the STATE of the record with the groupwise maximum SEQ; to obtain the duration, just sum the DURATION of each record in the group:

INSERT INTO Duration
  (Query_ID, Status, DURATION)
SELECT QUERY_ID, STATE, t.DURATION
FROM   INFORMATION_SCHEMA.PROFILING JOIN (
  SELECT   QUERY_ID, MAX(SEQ) AS SEQ, SUM(DURATION) AS DURATION
  FROM     INFORMATION_SCHEMA.PROFILING
  WHERE    QUERY_ID = 9
  GROUP BY QUERY_ID  -- superfluous in the presence of the above filter
) t USING (QUERY_ID, SEQ)

Upvotes: 1

Related Questions