How to optimize database query?

Question

MySQL

->addFrom("LEFT JOIN `betaintranet`.`ad_c`","`tgb`","`node`.`nid` = `tgb`.`obj_id` AND `tgb`.`start_date`<= NOW() AND `tgb`.`end_date` >= NOW() AND `tgb`.`ban_type` < '4' /* vrezka */")
      ->addField("COUNT(DISTINCT `tgb`.`bid`)","`tgbactiv`")			
      ->addField("`tgb`.`bid`","`tgbid`")
      ->addField("`tgb`.`phone`","`tgbphonebaze`")
    
    ->addFrom("LEFT JOIN `betaintranet`.`ad_banners`","`tgbinfo`","`tgb`.`bid` = `tgbinfo`.`bid`")
      ->addField("`tgbinfo`.`filename`","`tgbimageurl`")
      ->addField("`tgbinfo`.`url`","`tgburl`")
      ->addField("`tgbinfo`.`text`","`tgbtext`")
      ->addField("`tgbinfo`.`footer`","`tgbprice`")
      ->addField("`tgbinfo`.`header_text`","`tgbtitle`")

The full request is obscenely monstrous, but this jdin slows down the page for up to a minute.
But in fact it takes 1 picture 1 id 1 title 1 description ;(
as I understand it, it works very slowly < 4.
By specifying an explicit value of 2, the speed increases significantly. The
question is how best to make this request from a performance point of view.

Answer 1

NOW() NOT BETWEEN `tgb`.`start_date` AND `tgb`.`end_date`

To understand what slows down, you need to see the structure of the tables. As a universal option, you can try to transfer

`tgb`.`start_date`<= NOW() AND `tgb`.`end_date` >= NOW() AND `tgb`.`ban_type` < '4'

in WHERE.