How to view Query Execution time
Dear all, I want to know how much time did it take to run a sample query. In postgresql, we enable timing by \timing command. Is there is any way to enable in Mysql Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to view Query Execution time
Usually, at the end of the query running it displays the time how much it took. Or else enable the profiling and run the query to check the exact time it took for execution at all levels. On Mon, Aug 1, 2011 at 12:11 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, I want to know how much time did it take to run a sample query. In postgresql, we enable timing by \timing command. Is there is any way to enable in Mysql Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=sureshkumar...@gmail.comhttp://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: How to view Query Execution time
you can also use EXPLAIN, which will give you much more details. http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html http://www.techiequest.com/mysql-visual-explain-hierarchical-view-of-query-execution-plan/ On Sun, Jul 31, 2011 at 11:45 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Usually, at the end of the query running it displays the time how much it took. Or else enable the profiling and run the query to check the exact time it took for execution at all levels. On Mon, Aug 1, 2011 at 12:11 PM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I want to know how much time did it take to run a sample query. In postgresql, we enable timing by \timing command. Is there is any way to enable in Mysql Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=sureshkumar...@gmail.com http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: How to view Query Execution time
http://dev.mysql.com/doc/refman/5.1/en/query-log.html information on query log may become useful for you in the future. Also, search the slow query log, which also may help you in the future On Mon, Aug 1, 2011 at 11:54 AM, Prabhat Kumar aim.prab...@gmail.comwrote: you can also use EXPLAIN, which will give you much more details. http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html http://www.techiequest.com/mysql-visual-explain-hierarchical-view-of-query-execution-plan/ On Sun, Jul 31, 2011 at 11:45 PM, Suresh Kuna sureshkumar...@gmail.com wrote: Usually, at the end of the query running it displays the time how much it took. Or else enable the profiling and run the query to check the exact time it took for execution at all levels. On Mon, Aug 1, 2011 at 12:11 PM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I want to know how much time did it take to run a sample query. In postgresql, we enable timing by \timing command. Is there is any way to enable in Mysql Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=sureshkumar...@gmail.com http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Query execution time - MySQL
Hi, When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etc Thanks, Neil _ All new Live Search at Live.com http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
Re: Query execution time - MySQL
Greetings Niel, Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth. HOWEVER - Just so you know, if you execute the query MANUALLY via the *command-line of MySQL* it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer: mysql query or mysql run the query (use the below quoted/threaded example as a starting place to write your own query...?) Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be): http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself. TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information ***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. )) Let me know if you have any questions. Standing by and I hope this helped you. Sincerely, Craig Huffstetler xq on FreeNode #mysql | #apache On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote: Hi, When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etc Thanks, Neil _ All new Live Search at Live.com http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
RE: Query execution time - MySQL
Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. Regards Neil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself. TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apache On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote: Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today! http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/
Re: Query execution time - MySQL
If you using C++ then you can use this: http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this in my code, does an excelent job. Also you may want to look at the 'slow log' in mysql which will show, to the nearest second, the length of queries Ben Neil Tompkins wrote: Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. Regards Neil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself. TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apache On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote: Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today! http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query execution time - MySQL
Hi Neil, If your using Linux then you have to install the glib RPM's in the usual way. I don't know about other platforms, but I am sure there will be a version of glib out there... Also ensure the correct include and link directives are in your Makefile, which you can get (on Linux) using the commands: # glib-config --cflags # glib-config --libs Ben Neil Tompkins wrote: Thanks Ben, but I don't appear to have the header file glib.h in my libraries. Neil Date: Wed, 14 May 2008 12:39:09 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Query execution time - MySQL If you using C++ then you can use this: http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this in my code, does an excelent job. Also you may want to look at the 'slow log' in mysql which will show, to the nearest second, the length of queries Ben Neil Tompkins wrote: Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. Regards Neil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself. TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apache On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote: Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today! http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Miss your Messenger buddies when on-the-go? Get Messenger on your Mobile! http://clk.atdmt.com/UKM/go/msnnkmgl001001ukm/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe
RE: Query execution time - MySQL
Hi Ben I running on Windows. I think I need a solution where i can get the search time within my sql query. Is this possible. At the moment I;m running mysql server version 3.28 Neil Date: Wed, 14 May 2008 13:44:22 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query execution time - MySQL Hi Neil, If your using Linux then you have to install the glib RPM's in the usual way. I don't know about other platforms, but I am sure there will be a version of glib out there... Also ensure the correct include and link directives are in your Makefile, which you can get (on Linux) using the commands: # glib-config --cflags # glib-config --libs Ben Neil Tompkins wrote: Thanks Ben, but I don't appear to have the header file glib.h in my libraries.Neil Date: Wed, 14 May 2008 12:39:09 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Query execution time - MySQL If you using C++ then you can use this: http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this in my code, does an excelent job. Also you may want to look at the 'slow log' in mysql which will show, to the nearest second, the length of queries Ben Neil Tompkins wrote:Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. RegardsNeil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself.TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apacheOn Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote:Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today
RE: Query execution time - MySQL
Thanks for your help. In the end I've decided to use GetTickCount() Neil Date: Wed, 14 May 2008 13:44:22 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query execution time - MySQL Hi Neil, If your using Linux then you have to install the glib RPM's in the usual way. I don't know about other platforms, but I am sure there will be a version of glib out there... Also ensure the correct include and link directives are in your Makefile, which you can get (on Linux) using the commands: # glib-config --cflags # glib-config --libs Ben Neil Tompkins wrote: Thanks Ben, but I don't appear to have the header file glib.h in my libraries.Neil Date: Wed, 14 May 2008 12:39:09 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Query execution time - MySQL If you using C++ then you can use this: http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this in my code, does an excelent job. Also you may want to look at the 'slow log' in mysql which will show, to the nearest second, the length of queries Ben Neil Tompkins wrote:Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. RegardsNeil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself.TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apacheOn Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote:Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today! http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- MySQL General Mailing List For list
Re: Query execution time - MySQL
Neil Tompkins wrote: Thanks for your help. In the end I've decided to use GetTickCount() Neil Date: Wed, 14 May 2008 13:44:22 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query execution time - MySQL Hi Neil, If your using Linux then you have to install the glib RPM's in the usual way. I don't know about other platforms, but I am sure there will be a version of glib out there... Also ensure the correct include and link directives are in your Makefile, which you can get (on Linux) using the commands: # glib-config --cflags # glib-config --libs Ben Neil Tompkins wrote: Thanks Ben, but I don't appear to have the header file glib.h in my libraries.Neil Date: Wed, 14 May 2008 12:39:09 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Query execution time - MySQL If you us ing C++ then you can use this: http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this in my code, does an excelent job. Also you may want to look at the 'slow log' in mysql which will show, to the nearest second, the length of queries Ben Neil Tompkins wrote:Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. RegardsNeil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself.TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-b y-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apacheOn Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote: Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ _Great deals on almost anything at eBay.c o.uk. Search, bid, find and win on eBay today!http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL
Re: Query execution time?
joe mcguckin [EMAIL PROTECTED] wrote: Does the mysql server return to the client (DBI in this case) the query execution time? If so, how do I retrieve it? my $_startTime = time; $dbh-do(INSERT INTO dinner VALUES ('chicken','grille')); print Execution time: .(time - $_startTime).\n; Like that:) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query execution time?
As far as I know there isn't a way, though ideally there should be to do so. I would also look more for the answer and ask you to post it on the PER DBI mailinglist. Thanks Aman Raheja joe mcguckin wrote: Does the mysql server return to the client (DBI in this case) the query execution time? If so, how do I retrieve it? Thanks, Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query execution time?
Does the mysql server return to the client (DBI in this case) the query execution time? If so, how do I retrieve it? Thanks, Joe -- Joe McGuckin ViaNet Communications 994 San Antonio Road Palo Alto, CA 94303 Phone: 650-213-1302 Cell: 650-207-0372 Fax: 650-969-2124 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Execution Time in mysql
# [EMAIL PROTECTED] / 2003-06-24 20:32:48 -0700: insert into table1 select * from table2; Table2 has some 10,000 records while table1 has around 11,00,000 records. The time reqd. to execute the above query is round about 80- 90 secs. The table1 has 7 columns out of which 5 are composite primary keys. No other indexing is provided. I have also tried to use the optimize table table1 command but to no effect. does this help? http://www.mysql.com/doc/en/Insert_speed.html -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: Query Execution Time in mysql
I think we need more info: Table definition, MySQL version etc.. Med venlig hilsen Lars Geisler -Original Message- From: Roman Neuhauser [EMAIL PROTECTED] To: Amit Lonkar [EMAIL PROTECTED] CC: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Re: Query Execution Time in mysql Sent: on, 25 jun 2003 12:36:34 GMT Received: on, 25 jun 2003 12:40:14 GMT Read: on, 25 jun 2003 13:19:51 GMT # [EMAIL PROTECTED] / 2003-06-24 20:32:48 -0700: insert into table1 select * from table2; Table2 has some 10,000 records while table1 has around 11,00,000 records. The time reqd. to execute the above query is round about 80- 90 secs. The table1 has 7 columns out of which 5 are composite primary keys. No other indexing is provided. I have also tried to use the optimize table table1 command but to no effect. does this help? http://www.mysql.com/doc/en/Insert_speed.html -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Execution Time in mysql
You may want to try disabling the index during the insert: ALTER TABLE table1 DISABLE KEYS; insert into table1 select * from table2; ALTER TABLE table1 ENABLE KEYS; And see what that does. See: http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Amit Lonkar [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 9:33 PM To: [EMAIL PROTECTED] Subject: Query Execution Time in mysql Hi All, I have 2 tables say table1 and table2 in the database. I am using the following query to copy all the data from table2 to table1. insert into table1 select * from table2; Table2 has some 10,000 records while table1 has around 11,00,000 records. The time reqd. to execute the above query is round about 80- 90 secs. The table1 has 7 columns out of which 5 are composite primary keys. No other indexing is provided. I have also tried to use the optimize table table1 command but to no effect. Please let know if any solution is available. Thanks Amit Lonkar __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Execution Time in mysql
Hi All, I have 2 tables say table1 and table2 in the database. I am using the following query to copy all the data from table2 to table1. insert into table1 select * from table2; Table2 has some 10,000 records while table1 has around 11,00,000 records. The time reqd. to execute the above query is round about 80- 90 secs. The table1 has 7 columns out of which 5 are composite primary keys. No other indexing is provided. I have also tried to use the optimize table table1 command but to no effect. Please let know if any solution is available. Thanks Amit Lonkar __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Execution Time Perl/DBD
Hi. How do I get the query execution time from Perl/DBD? MySQL client returns this in the console window. Is this value available through the Perl DBD interface? Thanks. Gordan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Execution Time Perl/DBD
The obvious way to do this would be to get the time before and after the execution and then make a simple diff (after - before) I guess that is how the mysql client does it (butI'm not sure) I do not think that mysql itself provides this information.. how to get the time in Perl/DBD I do not know. but in java it would be long tb = System.currentTimeMillis(); // do the sql-statement long diff = System.currentTimeMillis() - tb; - Original Message - From: Gordan Bobic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 21, 2001 12:49 PM Subject: Query Execution Time Perl/DBD Hi. How do I get the query execution time from Perl/DBD? MySQL client returns this in the console window. Is this value available through the Perl DBD interface? Thanks. Gordan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Execution Time Perl/DBD
On Wednesday 21 Nov 2001 12:11, Christian Andersson wrote: The obvious way to do this would be to get the time before and after the execution and then make a simple diff (after - before) I guess that is how the mysql client does it (butI'm not sure) I do not think that mysql itself provides this information.. I was kind of hoping that the DBD object would have a special property where this can be read. Thanks. Gordan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Execution Time
Hi Within mysql, all queries return the time taken to execute, ie 20 rows in set (0.77 sec) 35 rows in set (1.33 sec) etc. Is it possible within perl, or C for that matter, to get the time taken for the MySql server to get the data ? Thanx Simon -- Simon Windsor CricInfo http://www.cricinfo.com/ Tel: +44 (0) 1249 700744 Fax: +44 (0) 1249 700725 Email: mailto:[EMAIL PROTECTED] This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. Thank you for your cooperation and assistance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to get Query Execution Time
hi there, at the mysql prompt i can get the exact time taken to run each query. how can i display this time in an HTML page via PHP. is there a function which gives me this time bye Mathew - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to get Query Execution Time
Hi, at the mysql prompt i can get the exact time taken to run each query. how can i display this time in an HTML page via PHP. is there a function which gives me this time Unfortunately, no, there isn't a built-in function to do this. So, you'll have to resort to some DIY. But, it's quite easy... Something like this should do the trick: ?php // Record start time. $start = doubleval(microtime()); // Do your MySQL query here... // Record the end time and print duration. $end = doubleval(microtime()); echo "Query took " . strval($end - $start) . " seconds!"; ? I'm not exactly sure whether doubleval() will convert microtime()'s "123456 450" return string value into a number like 123456.450, but I seem to remember it does. If not, you'll have to settle for a resolution of only whole seconds, by using time() instead. Regards, Basil Hussain ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php