Change query sql

Question

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'));
$lastDate  = date("Y-m-d", strtotime('last day of December 2016'));

for($i=strtotime($firstDate); $i<=strtotime($lastDate); $i+=86400 *7){
  array_push($dates, date("Y-m-d", strtotime('monday this week', $i)));
}

for($i = 0; $i < count($dates); $i++){

  $sql = "SELECT pr_products.product,
  CONCAT(YEAR('".$dates[$i]."'),'-',LPAD(WEEK('".$dates[$i]."'),2,'0')) AS Week,
  SUM(IF(sw_sowing.type = 'SW', sw_sowing.quantity,0)) AS PlantSowing,
  SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6      WEEKDAY('".$dates[$i]."')),'".$dates[$i]."'), sw_sowing.date)/7) >= pr_products.week_production AND sw_sowing.type = 'SW',sw_sowing.quantity,0)) AS              production
FROM (
      SELECT max(sw_sowing.id) AS id
      FROM sw_sowing
      WHERE sw_sowing.status != 0
      AND sw_sowing.id_tenant = :id_tenant
      AND sw_sowing.status = 100
      AND sw_sowing.date <= TIMESTAMPADD(DAY,(6-WEEKDAY('".$dates[$i]."')),'".$dates[$i]."')
     GROUP BY sw_sowing.id_production_unit_detail
     ) AS sw
     INNER JOIN sw_sowing ON sw_sowing.id = sw.id
     INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product
     INNER JOIN pr_varieties ON sw_sowing.id_variety = pr_varieties.id
     INNER JOIN pr_lands ON pr_lands.id = sw_sowing.id_land
     WHERE pr_varieties.code != 1
     AND sw_sowing.id_product = 1
     AND sw_sowing.status = 100
     GROUP BY pr_products.product
     HAVING plantSowing > 0
     ORDER BY pr_products.product";

}

I declare two variables initially that are $firstdate what is the start date and $lastDate which is the end date.

Then I make a for to go through the two dates and keep in an array the dates of Monday of each week.

Then I go through that new array to get the data I need from week to week.

Note: Within the query the variables $dates[$i] are the Monday dates of each week.

Anyway, the query works perfectly because it brings me the data I need from the 52 weeks of the year. The problem is that it takes a while.

I already indexed the tables in mysql, I improve a little but not enough, the query is not actually heavy it takes an average of 0.60 seconds per cycle.

I would like to know if there is a possibility of deleting the for what I am doing and within the query add I do not know, a WHERE that compares the two dates and brings me the data, or if there is any way to improve the query.

I already updated the query with the suggestions of the answer:

$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);

$sql = "SELECT product AS product,
            Week AS label,
            ROUND(SUM(harvest)/SUM(production),2) AS value
            FROM (
                    (
                        SELECT pr_products.product,
                               CONCAT(YEAR(:dates),'-', LPAD(WEEK(:dates1),2,'0')) AS Week,
                               SUM(IF(sw_sowing.type = 'SW', sw_sowing.quantity,0)) AS PlantSowing,
                               SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-WEEKDAY(:dates2)),:dates3), sw_sowing.date)/7) >= pr_products.week_production AND sw_sowing.type = 'SW',sw_sowing.quantity,0)) AS production,
                               0 AS Harvest
                        FROM (
                                SELECT max(sw_sowing.id) AS id
                                FROM sw_sowing
                                WHERE sw_sowing.status != 0
                                AND sw_sowing.date <= TIMESTAMPADD(DAY,(6-WEEKDAY(:dates4)),:dates5)
                                GROUP BY sw_sowing.id_production_unit_detail
                             ) AS sw
                        INNER JOIN sw_sowing ON sw_sowing.id = sw.id
                        INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product
                        INNER JOIN pr_varieties ON sw_sowing.id_variety = pr_varieties.id
                        WHERE pr_varieties.code != 1
                        AND sw_sowing.id_product = 1
                        AND sw_sowing.status = 100
                        AND sw_sowing.id_tenant = :id_tenant
                        GROUP BY pr_products.product
                        HAVING plantSowing > 0
                        ORDER BY pr_products.product
                    )
                    UNION ALL
                    (
                        SELECT  pr_products.product,
                                CONCAT(YEAR(:dates6),'-', LPAD(WEEK(:dates7),2,'0')) AS Week,
                                0 AS plantSowing,
                                0 AS Production,
                                SUM(pf_harvest.quantity) AS Harvest
                        FROM pf_harvest
                        INNER JOIN pr_products ON pr_products.id = pf_harvest.id_product
                        INNER JOIN pr_varieties ON pr_varieties.id = pf_harvest.id_variety
                        INNER JOIN pf_performance ON pf_performance.id = pf_harvest.id_performance
                        WHERE pf_harvest.date BETWEEN TIMESTAMPADD(DAY,(0-WEEKDAY(:dates8)),:dates9)
                        AND TIMESTAMPADD(DAY,(6-WEEKDAY(:dates10)),:dates11)
                        AND pr_varieties.code != 1
                        AND pf_harvest.id_product = 1
                        AND pf_performance.status = 100
                        AND pf_harvest.id_tenant = :id_tenant1
                        GROUP BY pr_products.product
                        ORDER BY pr_products.product
                        )
                    ) AS sc
            GROUP BY product, label
            ORDER BY label";

       $statement = $this->db->prepare($sql);
       $id_tenant = $this->getIdTenant();

       foreach($datePeriod AS $dates){

         $values = [
           ':dates'      => $dates->format('Y-m-d'),
           ':dates1'     => $dates->format('Y-m-d'),
           ':dates2'     => $dates->format('Y-m-d'),
           ':dates3'     => $dates->format('Y-m-d'),
           ':dates4'     => $dates->format('Y-m-d'),
           ':dates5'     => $dates->format('Y-m-d'),
           ':dates6'     => $dates->format('Y-m-d'),
           ':dates7'     => $dates->format('Y-m-d'),
           ':dates8'     => $dates->format('Y-m-d'),
           ':dates9'     => $dates->format('Y-m-d'),
           ':dates10'    => $dates->format('Y-m-d'),
           ':dates11'    => $dates->format('Y-m-d'),
           ':id_tenant'  => $id_tenant,
           ':id_tenant1' => $id_tenant
         ];

         $result = $this->db->executePrepared($statement , $values);
         $data[] = $result->fetchAll();
      }

$this -> jsonReturnSuccess($data);

Seems to me like you are hitting the N+1 problem common when you are developing queries that are iterating through the same sql. http://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem. My suggestion to you would be to refactor your query to use the weekday() function to identify Monday's (Monday is returned as a 0) and to retrieve all the rows into one dataset. I haven't analyzed your query and don't know your dataset well enough to refactor it for you, but It should be possible. Once you have the data in an array, you can then loop through it to get the data you need in PHP. — Dom DaFonte, Feb 06 '17 at 14:56
I am trying to refactor my query, I would to delete the for, but I think that it so difficult, thanks for you suggestion! — Fabian Sierra, Feb 06 '17 at 15:22
If you could provide some sample data for the tables being joined along with what the first few rows of what the resulting output would look like I can give it a shot. — Dom DaFonte, Feb 06 '17 at 16:01

score 1 · Accepted Answer · edited May 23 '17 at 11:45

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($datePeriod as $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($idArray as $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($datePeriod AS $dates) {
    //your code
}

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

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

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

foreach($datePeriod AS $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.

Thank you for your answer, I am going to look every step, about my query it is important to know every single monday of the year, because of them I have the "production", so the big problem is when I use for, I have to wait until this finished — Fabian Sierra, Feb 06 '17 at 16:16
@FabianSierra With DatePeriod, the first `for` isn't necessary, as you can see in my example. — Gabriel Heming, Feb 06 '17 at 16:19
Great!, Could you give me another example?. I am going to do this and I will tell you! — Fabian Sierra, Feb 06 '17 at 16:23
I've updated the example with a `foreach` loop. At least, the example is fully functional. — Gabriel Heming, Feb 06 '17 at 16:26
@FabianSierra you're welcome! Even you refactor your query, consider using `prepared statement`. It'll improve a lot the query execution. — Gabriel Heming, Feb 06 '17 at 17:57
I have tried to use prepare statement but I can do it, Could you give me another example? @GabrielHeming — Fabian Sierra, Feb 06 '17 at 20:52
I have a error wrong number of parameters, I don´t know why? @GabrielHeming — Fabian Sierra, Feb 07 '17 at 18:07
Some driver don't let you repeat the same name between named placeholder — Gabriel Heming, Feb 07 '17 at 18:44
I already updated the question with you suggest, but It doesn´t work! @GabrielHeming — Fabian Sierra, Feb 07 '17 at 19:44
Thanks @GabrielHeming it works! but the query still heavy, I am going to continue to review what happens — Fabian Sierra, Feb 08 '17 at 15:06

Rick James · Answer 2 · 2017-02-08T02:28:55.383

0

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

SELECT
       WEEK(date),
       AVG(...)
    FROM tbl
    JOIN ...
    WHERE date >= '2016-01-01'
      AND date  < '2016-01-01' + INTERVAL 1 YEAR
    GROUP BY 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 + INTERVAL 2 DAY) - 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              |
+-------------------------+

edited Feb 08 '17 at 02:28

answered Feb 07 '17 at 19:25

Rick James

135,179
13
127
222

But in my case I need the date in the `DATEDIFF`, I don´t know how to do it! – Fabian Sierra Feb 07 '17 at 19:44
Please explain the purpose of that `DATEDIFF`. And fix the syntax error: `...(6 WEEKDAY(...` – Rick James Feb 07 '17 at 20:06
In my particular case I need to know the dates of each Monday of the year 2016, to that date I need to subtract 6 weeks because this way the system is parameterized to know if the product is in production or not @RickJames – Fabian Sierra Feb 07 '17 at 20:10
A little bit of arithmetic with `TO_DAYS(date)` and `INTERVAL` will compute that. – Rick James Feb 08 '17 at 02:01
I am going to review how can I do it :) – Fabian Sierra Feb 08 '17 at 02:07
Sorry, I am not feeling energetic enough today. It may also involve `DAYOFWEEK()` to get back to Monday. – Rick James Feb 08 '17 at 02:13
Do not worry, can you give me an example?, or update your answer – Fabian Sierra Feb 08 '17 at 02:14
I made a stab it part of the question. – Rick James Feb 08 '17 at 17:34
jejej I don´t understand what you say! – Fabian Sierra Feb 08 '17 at 17:40

Change query sql

2 Answers2

Linked