如何减少多个查询的执行时间？

问题描述：

我有这段代码。基本上，它应该返回基于位置和什么位置的用户看了...

我试图找出如何缩短这个执行时间缴费作业列表...

$cities = $this->getChildren($location->city_id); 

     foreach ($cities as $child) { 
      $grandChildren = $this->getGrandChildren($child, false); 

      foreach ($grandChildren as $grandChild) { 
       $cities[] = $grandChild; 
      } 
     } 

     $menu = $this->getPositionChildren($postion); 
     $menu[] = $postion->menu_id; 

     // var_dump($cities); 

     foreach ($cities as $city) { 
      $city_id = (!empty($city) ? "AND FIND_IN_SET('{$city}', town)" : ''); 
      foreach ($menu as $key) { 
       $menu_id = (!empty($key) ? " AND FIND_IN_SET('{$key}', category_id)" : ''); 
       $queries[] = "SELECT ponuka_id as id FROM " . TABLE_PREFIX . "ponuky WHERE 1 {$city_id} {$menu_id} AND `published` = '1' ORDER BY `date` DESC"; 

       foreach ($queries as $query) { 
       $result = mysql_query($query); 

       if (!$result) { 
        return false; 
       } 

       while ($row = mysql_fetch_object($result)) { 
        if (!in_array($row->id, $this->_joblist, true)) { 
         $this->_joblist[] = $row->id; 
        } 
       } 
      } 
     }

上面的代码可以很容易地运行超过1000个查询，并且您可以想象，这需要时间......很长一段时间......有关如何改进此操作的任何提示？

从循环中删除您的查询！ –

避免使用FIND_IN_SET（）....一个更好的规范化数据库可以正确索引，然后数据库可以使用这些索引 –

看起来你必须标准化你的数据库 – Jens

答

代码现在看起来是这样，并采取查询圈外的运行速度快了很多......

$cities = $this->getChildren($location->city_id); 

     foreach ($cities as $child) { 
      $grandChildren = $this->getGrandChildren($child, false); 

      foreach ($grandChildren as $grandChild) { 
       $cities[] = $grandChild; 
      } 
     } 

     // $cities = $this->getCityInfoFromArray($cities); 
     $menu = $this->getPositionChildren($postion); 
     $menu[] = $postion->menu_id; 

     var_dump($cities); 

     foreach ($cities as $city) { 
      $city_id = (!empty($city) ? "AND FIND_IN_SET('{$city}', town)" : ''); 
      foreach ($menu as $key) { 
       $menu_id = (!empty($key) ? " AND FIND_IN_SET('{$key}', category_id)" : ''); 
       $queries[] = "SELECT ponuka_id as id FROM " . TABLE_PREFIX . "ponuky WHERE 1 {$city_id} {$menu_id} AND `published` = '1' ORDER BY `date` DESC"; 
      } 
     } 

     foreach ($queries as $query) { 
      $result = mysql_query($query); 

      if (!$result) { 
       return false; 
      } 

      while ($row = mysql_fetch_object($result)) { 
       if (!in_array($row->id, $this->_joblist, true)) { 
        $this->_joblist[] = $row->id; 
       } 
      } 
     }

您的查询仍在循环中。如果您想要更好的性能和更少的CPU利用率，请尝试彻底删除它。 –

恐怕这个是必要的，因为每个查询代表单个工作，然后将其放入工作列表数组...谢谢无论如何，你真的帮助。 – Kirito

答

，您可以尝试这样的。检查这是否适合你。

$city_id = ""; $city_id = ""; 
foreach ($cities as $city) { 
      $city_id .= (!empty($city) ? " AND FIND_IN_SET('{$city}', town)" : ''); 
      foreach ($menu as $key) { 
       $menu_id .= (!empty($key) ? " AND FIND_IN_SET('{$key}', category_id)" : ''); 
           } 
     } 

$result = mysql_query("SELECT ponuka_id as id FROM " . TABLE_PREFIX . "ponuky WHERE 1 {$city_id} {$menu_id} AND `published` = '1' ORDER BY `date` DESC");

这一个不工作...返回空数组，而不是数组作业... – Kirito

如何减少多个查询的执行时间？

相关推荐