Change Query Sql

I have the following query to obtain an average of a data of the 52 weeks of the year as follows: $dates = array(); $firstDate = date('Y-m-d', strtotime('first day of January 2016'

Solution 1:

You have to consider that your entire code needs to be improved. First of all, use the resources from PHP to get some improvement.

DatePeriod

To create the period between the first and the last monday of the year. Use DatePeriod:

$start = new \DateTime('first monday of this year');
$end = new \DateTime('first day of next year');//The last date is never reached, so if the year ends in a monday, you won't have any problem$datePeriod = new \DatePeriod($start , new \DateInterval('P7D') , $end);

foreach($datePeriodas$date)
{
    echo$period->format('Y-m-d');
}

It's very fast compared to your for loop.

array_push

Note: If you use array_push() to add one element to the array it's better to use $array[] = because in that way there is no overhead of calling a function.

As manual says, if it's to add an element into the array, use $array[].

Prepared Statement

Other common problem which I found, use prepared statement. It can be used to your query get a "pre-compiled" state (simple example):

$query = 'SELECT * FROM table WHERE id = :id';
$statement = $pdo->prepare($query);

$idArray = [1 , 2 , 3 , 4 , 5 , /** very long array, as your date list **/ ];
foreach($idArrayas$id)
{
    $statement->execute(array(':id' => $id));
    $result = $statement->fetchAll();
}

The N+1 problem

Another way is about the N+1 problem. If the others hints aren't enough to gain some speed, you can use native functions (array_map, array_walk, array_filter, etc...) to gather the values and do a single request.

Take a look at: What is SELECT N+1?https://secure.phabricator.com/book/phabcontrib/article/n_plus_one/

The Query

At last, I need more information about your query. You're using many mysql functions. It's the last plausible hint which I have. But, as you said, the query execution is fast. Try to take out those functions and check if the execution of script has been improved.

UPDATE

First of all, I think you're using so much PHP variable inside MySQL functions. If you have to take just the year and month (yyyy-mm), use DateTime::format().

$date->format('Y-m');//2017-02

There's a lot of example on manual.

As I said before, prepared statement is a kind of "pre-compiled" query. You have to write your query using placeholders (named or positional) instead of variables. The query above will be my example:

$query = "SELECT *
    FROM
        mytable 
        INNER JOIN mysecondtable ON (mytable.id = mysecondtable.id_mytable)
    WHERE
        mytable.date BETWEEN :start AND :end
        AND mytable.value >= :value;";

You already have the foreach:

$data  = array();

$start = new DateTime('first monday of January 2016');
$end   = new DateTime('last day of December 2016');

$datePeriod = new DatePeriod($start , new DateInterval('P7D') , $end);

foreach($datePeriodAS$dates) {
    //your code
}

Now, you have to "prepare" your query outside of your foreach loop:

$statement = $this->db->prepare($query);
foreach($datePeriodAS$dates) {
    //your code
}

And inside your foreach, just have to use the placeholders.

foreach($datePeriodAS$dates) {
    $values = [
        'start' => $dates->format('Y-m-d'),
        'end' => $dates->add(new DateInterval('P7D'))->format('Y-m-d'),//add 7 to reach a week'value' => 10
    ];

    $types = [
        'start' => Column::BIND_PARAM_STR,
        'end' => Column::BIND_PARAM_STR,
        'value' => Column::BIND_PARAM_INT
    ]

    //Phalcon PDO Adapter method$result = $connection->executePrepared($statement , $values , $types);//The result is a PDOStatement object$data[] = $result->fetchAll();
}

With these tips, you can improve a lot the execution time of your script.

Solution 2:

No loop, virtually no PHP code, only one SQL:

SELECT
       WEEK(date),
       AVG(...)
    FROM tbl
    JOIN ...
    WHEREdate>='2016-01-01'ANDdate<'2016-01-01'+INTERVAL1YEARGROUPBY week(date);

There may be some details I left out, but perhaps this will point you in a direction that helps simplify your code.

The Monday 6 weeks ago (starting with today, which is a Tuesday):

mysql>SET@d := CURDATE();
mysql>SELECT@d, DAYOFWEEK(@d), TO_DAYS(@d),
              TO_DAYS(@d+INTERVAL2DAY) - DAYOFWEEK(@d) AS ThisMon;
+------------+---------------+-------------+---------+|@d| DAYOFWEEK(@d) | TO_DAYS(@d) | ThisMon |+------------+---------------+-------------+---------+|2017-02-07|3|736732|736731|+------------+---------------+-------------+---------+
mysql>SELECT FROM_DAYS(736731-6*7);
+-------------------------+| FROM_DAYS(736731-6*7) |+-------------------------+|2016-12-26|+-------------------------+