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

Answer 1

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.

Answer 2

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?

Answer 3

Have you seen this Q&A on ServerFault?

How do I profile MySQL?