Geek Answers Handbook

What is the equivalent query in mysql?

Request 1: Top 10 Codes That Take The Most 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: 10 best codes that occupy maximum physical_pages

 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

+6
mysql sql-server
source share
4 answers

In MySQL, you need to commit this information from a log file, not through a query. Someone will probably tell you that a request is possible, but they are not fair to you. Cm:

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

.. significant enough if you ask this question, you do not want to use it.

So now your question becomes "how do you do this with the log file?". The number of physical reads for a query is not logged in MySQL stock versions. However, it is available on Percona Server. Amplification is awesome (even if I'm biased, I work in Percona):

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

The next question: how do you summarize the log to find this data. For this, I suggest mk-query-digest. http://www.maatkit.org/doc/mk-query-digest.html

+5
source share

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

+1
source share

Have you seen this Q&A on ServerFault?

How do I configure MySQL?

+1
source share
 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
0
source share

More articles:


All Articles