what is the equivalent query in mysql?

Question

Query 1: Top 10 codes that takes maximum time

select top 10 
  source_code,
  stats.total_elapsed_time/1000000 as seconds,
  last_execution_time from sys.dm_exec_query_stats as stats
cross apply(SELECT 
              text as source_code 
            FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
order by total_elapsed_time desc

Query2: Top 10 codes that takes maximum physical_reads

select top 10 
  source_code,
  stats.total_elapsed_time/1000000 as seconds,
  last_execution_time from sys.dm_exec_query_stats as stats
cross apply(SELECT 
              text as source_code 
            FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
order by total_physical_reads desc

taken from this article

Thank you John for Editing, that's should make the question clearer: how to get : Top 10 codes that takes maximum time & Top 10 codes that takes maximum physical_reads in MYSQL. — Bart, Aug 03 '10 at 02:43

score 5 · Accepted Answer · answered Aug 09 '10 at 20:33

In MySQL you need to capture this information from a log file, and not via a query. Someone will probably tell you a query is possible, but they're not being fair to you. See:

http://dev.mysql.com/doc/refman/5.1/en/log-tables.html "Currently, logging to tables incurs significantly more server overhead than logging to files."

.. significant enough that if you are asking this question, you don't want to use it.

So now your question becomes "how do you do this with a log file?". The number of physical reads for a query is not recorded in the stock-MySQL releases. It's available in Percona Server though. The enhancement is awesome (even if I'm biased, I work for Percona):

http://www.percona.com/docs/wiki/patches:slow_extended

The next question becomes how do you aggregate the log so you can find these details. For that, I suggest mk-query-digest. http://www.maatkit.org/doc/mk-query-digest.html.

score 1 · Answer 2 · edited May 23 '17 at 12:25

1

SELECT TOP 10 ... is SELECT ... LIMIT 10 in MySQL. If you are asking about CROSS APPLY that's not too different from INNER JOIN, see When should I use Cross Apply over Inner Join?

edited May 23 '17 at 12:25

Community

1
1

answered Aug 03 '10 at 02:27

chx

11,270
7
55
129

actually sam is asking about how to find the SQL queries that have taken the longest to execute. – Peter Recore Aug 03 '10 at 16:36

score 1 · Answer 3 · edited Apr 13 '17 at 12:13

1

Have you seen this Q&A on ServerFault?

How do I profile MySQL?

edited Apr 13 '17 at 12:13

Community

1
1

answered Aug 06 '10 at 20:21

Joe Stefanelli

132,803
19
237
235

score 0 · Answer 4 · answered Aug 09 '10 at 04:18

0

select 
source_code,
stats.total_elapsed_time/1000000 as seconds,
last_execution_time from sys.dm_exec_query_stats as stats
inner join(SELECT 
          text as source_code 
        FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
order by total_elapsed_time desc 
limit 10

select
source_code,
stats.total_elapsed_time/1000000 as seconds,
last_execution_time from sys.dm_exec_query_stats as stats
inner join(SELECT 
          text as source_code 
        FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
order by total_physical_reads desc
limit 10

answered Aug 09 '10 at 04:18

C_Rance

661
12
25

Please correct me if I'm wrong, but I don't think those dynamic management objects (sys.dm_exec_query_stats, sys.dm_exec_sql_text) exist in MySQL. – Joe Stefanelli Aug 09 '10 at 16:02
oic, sorry. I never notice that part.Missed out and overlook as a normal table – C_Rance Aug 10 '10 at 13:38

what is the equivalent query in mysql?

4 Answers4