<?php
include ('include/php/sql.php');

//Generate DummyData.

//
// CREATE TABLE showTable(id int(10), city varchar(25), `show` varchar(25));
//
if (1 == 2){ //change to generate data
    $shows =  array("Show1", "Show2", "Show3", "Show4", "Show5", "Show6", "Show7", "Show8", "Show9");
    $cities =  array("A","B","C","D","E","F","G","H","I");
    $sql = new SQL();
    $values = array();

    for ($i=1; $i<=250000; $i++){
       $c = mt_rand(0,8);
       $s = mt_rand(0,8);

       $values[] = "(".$i.", '".$cities[$c]."', '".$shows[$s]."')";

       if (count($values) < 250){
           //do nothing
       }else{
           $query = "INSERT INTO showTable (id, city, `show`) VALUES ".implode(", ", $values);
           $sql->query($query);

           $values=array();
       }
    }
}

//Run each query 100 times, measuare average time.

$sql = new SQL();
$dognose_times = array();

$runCount = 50;

for ($i = 0; $i<$runCount; $i++){
    $start = microtime(true);
    //dognose's version

    $sql->query("SELECT * FROM (
                    SELECT city, `show`, count(id)  FROM showTable GROUP BY city,`show` ORDER BY count(id) DESC
                    ) as temp GROUP BY city");

    $time_elapsed_secs = microtime(true) - $start;
    $dognose_times[] = $time_elapsed_secs;
}

$sql = new SQL();
$strawberry_times = array();
for ($i = 0; $i<$runCount; $i++){
    $start = microtime(true);
    //strawberrys's version

    $sql->query(" SELECT x.*
                      , z.total
                   FROM showTable x
                   JOIN
                      ( SELECT city, MAX(id) max_id FROM showTable GROUP BY city ) y
                     ON y.city = x.city
                    AND y.max_id = id
                   JOIN (SELECT city, `show`, COUNT(*) total FROM showTable GROUP BY city,`show`) z
                     ON z.city = x.city
                    AND z.`show` = x.`show`;");

    $time_elapsed_secs = microtime(true) - $start;
    $strawberry_times[] = $time_elapsed_secs;
}

//Show average.
echo "<hr>";
echo "Average dognose Query: ".array_sum($dognose_times) / count($dognose_times)."<br />";
echo "Average strawbe Query: ".array_sum($strawberry_times) / count($strawberry_times)."<br />";