There are several queries to the database. In total, they take more than 10 seconds to complete.

"SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2015-12-01" AND reviews_data<="2015-12-31" AND reviews_author="user" AND login<>"user"", duration=1.4160809516907 "SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2016-01-01" AND reviews_data<="2016-01-31" AND reviews_author="user" AND login<>"user"", duration=1.4150810241699 "SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2016-02-01" AND reviews_data<="2016-02-31" AND reviews_author="user" AND login<>"user"", duration=1.2920739650726 "SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2016-03-01" AND reviews_data<="2016-03-31" AND reviews_author="user" AND login<>"user"", duration=1.6590950489044 "SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2016-04-01" AND reviews_data<="2016-04-31" AND reviews_author="user" AND login<>"user"", duration=1.6340939998627 "SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2016-05-01" AND reviews_data<="2016-05-31" AND reviews_author="user" AND login<>"user"", duration=1.5760898590088 "SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2016-06-01" AND reviews_data<="2016-06-31" AND reviews_author="user" AND login<>"user"", duration=1.6550948619843 "SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2016-07-01" AND reviews_data<="2016-07-31" AND reviews_author="user" AND login<>"user"", duration=1.556088924408 "SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2016-08-01" AND reviews_data<="2016-08-31" AND reviews_author="user" AND login<>"user"", duration=1.3690779209137 "SELECT COUNT(reviews_data) as cnt FROM tblkworkreviews WHERE reviews_data>="2016-09-01" AND reviews_data<="2016-09-31" AND reviews_author="user" AND login<>"user"", duration=1.4540841579437

Tell me how to combine them into one?

  • 3
    select date_format(reviews_data, '%Y-%m'), count(1) ... group by date_format(reviews_data, '%Y-%m') - Mike
  • It works, but if for some months there is no data, then the request skips them, but you need 0. How can this be taken into account in your request? - SmallSani am
  • one
    Build the reference table of the months. Or generate it dynamically in a subquery or, if the version allows, in the CTE. - Akina
  • one
    @SmallSani Then, as Akina said, you need some reference request, for example, how here ru.stackoverflow.com/q/536013/194569 (the sequence number can be used for + interval X month, to the starting date, which would generate months) - Mike
  • one
    The code that fills the missing elements into an array is one simple loop. - Ipatiev

1 answer 1

You can do this:

  1. Select all lines for the author reviews_author="user" AND login<>"user" (replacing logins with id's and building indexes will most likely be able to speed up the query).
  2. Use the second part of the condition to get the value 1 or 0 and the aggregate function sum .

To solve such problems, you need to imagine what is happening in the database when you request and how you can "cut corners". In the general case, when querying, the database iterates over the rows from the table and compares their values ​​with the where constraints. If there are suitable indexes, the number of rows to be searched can be reduced by the base.

That is, it turns out that every request you have is a loop of the type:

 $cnt = 0; foreach ($rows as $row) { if ($row == $where) { $cnt++; } }

The more lines you have to iterate, the longer this cycle takes. In your case, with some probability (to know for sure, use the explain utility), the same sets of lines are searched.

 $cnt1 = 0; foreach ($rows as $row) { if ($row == $where1) { $cnt1++; } } $cnt2 = 0; foreach ($rows as $row) { if ($row == $where2) { $cnt2++; } } ...

If you think about it, you can optimize it, considering everything in one run.

 $cnt1 = 0; $cnt2 = 0; foreach ($rows as $row) { if ($row == $where1) { $cnt1++; } if ($row == $where2) { $cnt2++; } }

This is my decision. A slightly different way (but essentially identical) is suggested in the comments.

UPD for those in the tank :-)

 select sum(`reviews_data` >= "2015-12-01" and `reviews_data` <= "2015-12-31") as `cnt1` sum(`reviews_data` >= "2016-01-01" and `reviews_data` <= "2016-01-31") as `cnt2` from `tblkworkreviews` where `reviews_author` = "user" and `login` <> "user";
  • The cycle is actually one. In it we generate months and we run them in request. - SmallSani
  • If the period is large, the number of requests grows incredibly. - SmallSani
  • Those. Your method is already used, you just need to get away from it, making not a few requests to the database, but only one. - SmallSani
  • Mike in the question commentary has almost solved the problem, but empty entries are also needed ... - SmallSani
  • one
    @ EgorBanin Your request will have to write these same sum () separately for each month. The usual group by is more efficient and you don't need to write sum () for each month in advance - Mike