2

i want to test actual speed of script. I have run below query. 

 $query="select * from test";

  $msc = microtime(true);
  for ($i = 0; $i <= 99999; $i++)
        $rs = mysql_query($query);
    $msc = microtime(true) - $msc;
    echo $msc . ' s'; // in seconds
    echo "<br>";
    echo ($msc * 1000) . ' ms'; // in milliseconds

I try above code with different optimization queries. still have same output
$query="select * from stud"; $query2="select name from stud"

have same result run both query run thousand time using for loop.. please is there any other proper way to find out actual execution time of script.

Nitesh Pawar
  • 435
  • 2
  • 11

3 Answers3

0

What if you write your code in second.php file and Let first.php file to calculate the actual execution time of script.

# file: `first.php`
$init_time = time();
# Execute Your Script `second.php`
# Assuming php is properly installed
$output = `php second.php`;
$exit_time = time();
echo $exit_time - $init_time;

Edit: May be, You are Right, You will get same execution time because in both cases, You are just Running those MySQL Queries 1000 times. You are not processing data returned by PHP-MySQL Driver.

ssp singh
  • 152
  • 1
  • 14
  • I create two different scripts, test1.php and test2.php only change in query and run both scripts in different window of browser.. and run that script automaticaly after 5 second.. using , observ oupt arrond 30 minutes. – Nitesh Pawar Dec 17 '15 at 06:09
  • But, To execute this `PHP Script`, You don't need a browser. – ssp singh Dec 17 '15 at 10:28
  • I just want to know how much time it will take if when I execute mysql_query in php script. I have table contain more million records there. do u understand what i am looking. – Nitesh Pawar Dec 18 '15 at 06:40
  • Please, Understand it is not about, How many times query executed, it is about, How many records did you fetched and What you did with them. My code will give you exactly what you are looking for, `time taken by PHP script when executed`, I use this script to test speed of different Algorithms for same JOB. – ssp singh Dec 18 '15 at 07:17
0

You get good enough timings with a single iteration. However,...

The first execution of a query may be a lot slower than the second (and all subsequent calls -- very consistent timings). The first execution may hit cold caches -- hence more I/O to be done. So...

I would time the second execution of the same statement. But...

If you have the "Query cache" turned on, the second one will be "instantaneous" so not a good timing. So...

Always perform timing tests thus: SELECT SQL_NO_CACHE ....

Other issues...

Do not use the deprecated mysql_* interface; change to mysqli_* or PDO.

In your particular code, you probably should have divided by 100000 because of the repetitions.

For something like a MySQL statement, the overhead of the FOR loop is only a small percentage. But if you time some faster function (most PHP builtin functions), you need to estimate the FOR loop time and subtract it off. (That can be done by timing an empty loop. PHP is not smart enough to optimize out useless code. Nor is MySQL at the statement level. MySQL will clean up things like WHERE 1=1 AND ....)

(I know all this because I usually pepper my PHP code with microtime(true) calls. Usually I do it at the subroutine level; later drill down to individual SQL queries if necessary.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

You initially said that you want to measure PHP code execution speed, but I am afraid that what you're actually after is benchmarking different SQL queries, so let's cover both cases here.

Measure PHP code execution speed

Plain and simple, use timeit to measure PHP speed. It's not a straightforward task to write code to time code, and there's no need to roll your own.

Measure SQL speed

In this case, you should try to go as direct and close to the MySQL server as possible. This mean running queries in the database server, and not using PHP/webservers, because these can add their own overhead. A good and convenient way to do this is by using the mysql CLI client:

$ mysql -vvv -q -e 'SELECT SQL_NO_CACHE nid FROM node' drupal | grep 'rows in set'
87281 rows in set (0.00 sec)
$ mysql -vvv -q -e 'SELECT SQL_NO_CACHE * FROM node' drupal | grep 'rows in set'
87281 rows in set (0.02 sec)

Notes:

  1. It is pointless to do SQL speed measurement in a busy database. Use an isolated server and instance for that
  2. Run the query a couple times. Discard the fastest and slowest samples. Average the rest
  3. Do not forget the SQL_NO_CACHE directive and the -q parameter
  4. Due to network/server/MySQL jitter, I wouldn't bother with repeatable measurement differences below the 0.01 range.
flaviovs
  • 559
  • 7
  • 9