Hello! I have the following code:

$key_cache = 'cache_banners_tpl_'. $idpl; $dateNow = date(MYSQL_DATE_FORMAT); $dateTimeNow = date("H:i:s"); if(($banners = $this->app->cache5min->get($key_cache)) === NULL) { $find_items = array(); $banners = $this->app->db->fetchAll("SELECT b.id,b.active,b.richmedia,b.rich_position,b.ip_limit,b.cookie_limit,b.cookie_interval,b.day_limit,b.limit_interval,b.frequency, b.close_btn, b.use_geo, bpl.s_x, bpl.s_y FROM bs_items_places AS bp LEFT JOIN bs_items AS b ON b.id = bp.item_id LEFT JOIN bs_places AS bpl ON bpl.id = bp.place_id WHERE b.active = 1 AND b.date_start < '".$dateNow."' AND b.date_stop > '".$dateNow."' AND bpl.active = 1 AND bp.place_id = {$idpl} AND (IF((time_from!='00:00:00' AND time_to!='00:00:00'),(time_from<='".$dateTimeNow."' AND time_to>='".$dateTimeNow."'),1)) GROUP BY b.id ORDER BY b.frequency DESC, b.day_limit DESC "); foreach($banners AS $bnr) { $find_items[$bnr['id']] = $bnr; } $banners = $find_items; $this->app->cache5min->save($key_cache,$banners); }

EXPLAIN query:

 explain SELECT `bs_items_ip`.`id`, `bs_items_ip`.`item_id`, `bs_items_ip`.`place_id`, `bs_items_ip`.`date_show`, `bs_items_ip`.`ip`, `bs_items_ip`.`shows` FROM `bs_items_ip` WHERE `bs_items_ip`.`item_id` = 146 AND `bs_items_ip`.`ip` = '94.100.184.101' AND `bs_items_ip`.`date_show` = '2017-05-29' LIMIT 1; +----+-------------+-------------+------+---------------+---------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+---------+---------+-------+--------+-------------+ | 1 | SIMPLE | bs_items_ip | ref | item_id | item_id | 5 | const | 580178 | Using where | +----+-------------+-------------+------+---------------+---------+---------+-------+--------+-------------+ 1 row in set (0.00 sec)

it loads me very much the processor, is it possible to somehow optimize it? So that the processor does not load, because of this, the site does not work well. As soon as I remove and comment on this code, the load immediately drops.

  • one
    First you need to make EXPLAIN ТУТ_ТВОЙ_ЗАПРОС ..... and see the results ... secondly, you need to see if you have indexes in the tables - Alexey Shimansky
  • And time_from time instead of zeros can not be done on NULL ? - teran
  • Added explain - DronaxD
  • one
    You explain explain for that request on which a question. Now for some reason you have made it for another query on one table and even with a limit. and format it to be readable (as the code highlight) - Mike
  • This is one of the requests being fulfilled, is it possible to somehow prevent these 500+ entries from recording - DronaxD

2 answers 2

At a minimum, I can advise on the sql query:

  1. Do not use the left join when you impose conditions on the join table and then in where , so your left join turns into a join . Write the usual join right away, it works faster.

  2. Why use group by without aggregating functions? This causes an extra load.

  3. You can think about your IF in WHERE , perhaps this condition can be rewritten much easier.

As a result, your request will be something like this:

 SELECT b.id, b.active, b.richmedia, b.rich_position, b.ip_limit, b.cookie_limit, b.cookie_interval, b.day_limit, b.limit_interval, b.frequency, b.close_btn, b.use_geo, bpl.s_x, bpl.s_y FROM bs_items_places AS bp JOIN bs_items AS b ON b.id = bp.item_id JOIN bs_places AS bpl ON bpl.id = bp.place_id WHERE b.active = 1 AND b.date_start < '".$dateNow."' AND b.date_stop > '".$dateNow."' AND bpl.active = 1 AND bp.place_id = {$idpl} AND (IF((time_from!='00:00:00' AND time_to!='00:00:00'), (time_from<='".$dateTimeNow."' AND time_to>='".$dateTimeNow."'),1)) ORDER BY b.frequency DESC, b.day_limit DESC
  • It did not help, the load still does not fall - DronaxD
  • Make @DronaxD, EXPLAIN query and add the result to your question. - Visman
  • Added explain, returns 580 thousand results is a lot - DronaxD
  1. Replace LEFT JOIN with INNER - all the same, the bindings are internal (the WHERE section is like this);
  2. Better yet - rewrite to a Cartesian with selections;
  3. Add table aliases to EACH field (or it is not clear from which table the time_from and time_to fields are taken);

  4. And then watch EXPLAIN.

     SELECT b.id, b.active, b.richmedia, b.rich_position, b.ip_limit , b.cookie_limit, b.cookie_interval, b.day_limit, b.limit_interval , b.frequency, b.close_btn, b.use_geo, bpl.s_x, bpl.s_y FROM bs_items_places AS bp, bs_items AS b, bs_places AS bpl WHERE b.active = 1 AND b.id = bp.item_id AND bpl.id = bp.place_id AND b.date_start < '".$dateNow."' AND b.date_stop > '".$dateNow."' AND bpl.active = 1 AND bp.place_id = {$idpl} AND (IF((time_from != '00:00:00' AND time_to != '00:00:00'), (time_from <= '".$dateTimeNow."' AND time_to >= '".$dateTimeNow."'), 1)) GROUP BY b.id ORDER BY b.frequency DESC, b.day_limit DESC

Well, I fully agree with Denis - it makes sense to remove the grouping. And if you only need one entry for each b.id, then leave it, and wrap the bpl.s_x, bpl.s_y fields with any group function (say, MIN).

Also, if a PHP server with a MySQL server is in the same time zone, then remove the substitution of the current date and time from PHP and insert the corresponding MySQL functions.

  • "In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents" The first two items are one entity. Well, "Cartesian" is generally an interesting term (everyone understands the extent of his depravity) :) - tutankhamun
  • @tutankhamun That's it. Just when a query is written using JOIN, the conditions of binding are “smeared” in the query text, but when the direct (Cartesian, Cartesian) product of tables is used, then all the selection conditions (we are not talking now, of course, about the HAVING section) appear in one block - this somewhat simplifies the construction of the "correct" indices for a given query. - Akina