CODEWITHSUNDEEP
mysqlsql-server
Bart
Bart

Reputation: 4920

what is the equivalent query in mysql?

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

Upvotes: 7

Views: 5398

Answers (4)

Morgan Tocker
Morgan Tocker

Reputation: 3438

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.

Upvotes: 5

C_Rance
C_Rance

Reputation: 661

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

Have you seen this Q&A on ServerFault?

How do I profile MySQL?

Upvotes: 1

chx
chx

Reputation: 11790

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?

Upvotes: 1

Related Questions