Again, how to calculate cpu time.

[lcs01]

Last October, I asked a question here about how to calculate cpu time a program consumes Thread 1294251 (

http://www.tek-tips.com/viewthread.cfm?qid=1294251).

With Kevin and Miller's help, I got that one solved. Thanks to Kevin and Miller!

Now I am having a similar question. This time I am tuning lots of sql statements in a perl code. By executing the old and new sql's from MySQL prompt:

mysql> SELECT ...... FROM ...... WHERE ......; // An old sql
// results here ommitted.
[COLOR=red]1 row in set (5.49 sec) [/color]

mysql> SELECT ...... FROM ...... WHERE ......; // A new sql
// results here ommitted.
[COLOR=red]1 row in set (0.23 sec) [/color]

you can see the performance is improved significantly.

However, when I used Time::HiRes module (recommended by Kevin in Thread 1294251 in my mod_perl w/ DBI, the cpu time is about the same (both are less than 0.5 sec). And I did notice that it took a long time to display a web page while using the old sql in my mod_perl code. But why is the cpu time it consumed about the same as the new code did?

In addition, in DBI, is there a way to catch how much cpu time consumed by executing a sql, i.e. the red line in the above two examples(5.49 & 0.23)?

 

[MillerH]

In my opinion the best benchmarking of MySQL statements is done in MySQL itself and not in perl. To get faster queries, just make judicious use of the "EXPLAIN" function. Add appropriate INDEXES. And definitely check the "Slow Query Log" occasionally for queries that may start taking longer as the size of tables increase.

- Miller