To get sub-second accuracy, you will need Time::HiRes, as suggested.

However, please note that you are not going to be showing a particularly accurate sub-second figure as this will not take into account the time it takes for the query to be sent out to the MySQL socket and the result set to be brought back. So, any result will be query time plus 'other overheads'.

Bear in mind also that the first time that a query is run, it will take longer than if you run it again straight away as the MySQL server may cache the query.

With all that said, if we were only interested in measuring whole seconds (like running time, as suggested), we can get a greater accuracy by using a stored procedure (MySQL 5.x and greater only.)

Rather than marking time, sending off the query, receiving the results, marking time, taking the difference, we can do the timing within MySQL itself. Consider the following SQL:

drop procedure if exists ttest;
delimiter //
create procedure ttest()
BEGIN
  DECLARE stime int;
  DECLARE etime int;
  
  /*
    Mark start time.
  */
  select unix_timestamp() into stime;

  /*
    Your query goes here.
  */
  show tables;

  /*
    Mark end time.
  */
  select unix_timestamp() into etime;

  /*
    Calculate time elapsed
    (integer seconds only.)
  */
  select etime-stime as elapsed_time;
END
//
delimiter ;

[download]

What we are doing is storing the current UNIX time into a variable, running our main query, storing the UNIX time into another variable and then selecting the difference in seconds. The time we are recording is time taken by the MySQL server with no Perl/DBI/socket overhead.

The query to run this from Perl would be call ttest();. Note that doing this returns 2 result sets, for which your DBI code should be written accordingly. (Here is a node describing this. Google will find you other references.)

I have to say that the above is probably only of academic interest as I generally find that query times are sub-second (therefore would return elapsed_time=0), even when looking at multi-Gb tables. But it's another way to do it ;-)

References

Here is a teaser for the article "MySQL 5.0 New Features: Stored Procedures" by Peter Gulutzan, on the MySQL site. The full article is available as a PDF and is worth reading for anyone starting out with MySQL SP's.

In reply to Re: MySQL Query Time DBI by smiffy
in thread MySQL Query Time DBI by KynkoKat

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.