php - 加快循环内的查询速度？

Question

我正在获取 xml feed、解析并存储到数据库。

有时会有一些欧洲球队，例如哪个国家是欧洲，而不是英格兰、德国、塞尔维亚等。只是欧洲，所以我创建了包含所有国家/地区的数组和 map 表，其中包含具有国家/地区列的所有球队。

所以我想检查表 map 内的团队，其中国家= $countryfromarray，例如，当我尝试与一个团队合作时，这会起作用，但我从提要中获取团队，提要中大约有 5000 个团队。

if((strtolower($country) == 'southamerica') or (strtolower($country) == 'conmebol')){
        $countries = $this->conmebolarray;
        $isregion = true;
    }
    elseif((strtolower($country) == 'europe') or (strtolower($country) == 'uefa')){
        $countries = $this->euroarray;
        $isregion = true;
    }
    elseif((strtolower($country) == 'asia') or (strtolower($country) == 'afc')){
        $countries = $this->afcarray;
        $isregion = true;
    }
    elseif((strtolower($country) == 'africa') or (strtolower($country) == 'caf')){
        $countries = $this->cafarray;
        $isregion = true;
    }
    elseif((strtolower($country) == 'northandcentralamerica') or (strtolower($country) == 'concacaf')){
        $countries = $this->conarray;
        $isregion = true;
    }
    else{
        $countries = $country;
        $isregion = false;
    }
    //$res = '';
    if($isregion){
      $query =  $PDO->prepare($sql);
      $newcountry = '';
      foreach($countries as $loopcountry){
          $query->bindValue(':data', $data);
          $query->bindValue(':country', $loopcountry);
          //
          if($query->execute())
          {
            if($query->rowCount()>0){
              $res = $query->fetchColumn();
              $newcountry = $loopcountry;   
              break; 
            }
            $query->closeCursor(); 
          }
      }

和国家数组:

    //"southamerica" & "conmebol" & "CONMEBOL";
public $conmebolarray = array('argentina','brazil','chile','colombia','paraguay','uruguay','venezuela','peru','bolivia','ecuador','newzealand');

//"europe" & "uefa" & "Europe" & "UEFA";
public $euroarray = array('england','france','germany','italy','spain','scotland','albania','andorra','armenia','austria','azerbaijan','belarus','belgium','bosnia','bulgaria','croatia','cyprus','czech','denmark','estonia','faroeislands','georgia','gibraltar','greece','hungary','iceland','israel','kazakhstan','latvia','liechtenstein','luxembourg','malta','moldova','montenegro','netherlands','northernireland','norway','poland','portugal','ireland','romania','russia','sanmarino','serbia','slovakia','slovenia','sweden','switzerland','turkey','ukraine','wales','europe');

//"asia" & "afc" & "Asia" & "AFC";
public $afcarray = array('australia','japan','korea','singapore','china','qatar','saudiarabia','vietnam','bahrain',
'bangladesh','bhutan','brunei','cambodia','chinesetaipei','guam','hongkong','india','indonesia','iran','iraq','japan','korea',
'northkorea','kuwait','kyrgyzstan','laos','lebanon','macau','malaysia','maldives','mongolia','myanmar','nepal',
'northernmarianaislands','oman','pakistan','palestine','philippines','srilanka','syria','tajikistan','thailand','timorleste','turkmenistan','uae','uzbekistan','yemen','newzealand','asia','afc');

//"africa" & "caf" & "Africa" & "CAF";
public $cafarray = array('nigeria','southafrica','egypt','tunisia','algeria','morocco','cameroon','drcongo','ivorycoast','ghana','djibouti','eritrea','ethiopia','kenya','rwanda','somalia','southsudan','sudan','tanzania','uganda','zanzibar','angola',
'botswana','comoros','lesotho','madagascar','malawi','mauritius','mozambique','namibia','seychelles','swaziland','zambia',
'zimbabwe','reunion','burundi','saotomeandprincipe','gabon','equatorialguinea','congo','chad','centralafricanrepublic','togo',
'sierraleone','senegal','niger','mauritania','mali','liberia','guineabissau','guinea','gambia','capeverde','burkinafaso',
'benin','libya','africa','caf');

//"northandcentralamerica" & "concacaf" & "CONCACAF";
public $conarray = array('usa','mexico','canada','costarica','elsalvador','guatemala','honduras','trinidadandtobago','jamaica',
'panama','honduras','haiti','cuba','belize','dominicanrepublic','bermuda','aruba','barbados','grenada','surinam',
'guadeloupe', 'antigua','antiguaandbarbuda','saintvincent','saintvincentandthegrenadines','saintkittsandnevis',
'saintlucia','nicaragua','curacao','puertorico','guyana','dominica','usvirginislands','montserrat','caymanislands',
'turksandcaicos','turksandcaicosislands','britishvirginislands','bahamas','anguilla','frenchguiana','martinique',
'saintmartin','sintmarteen','bonaire','concacaf','northandcentralamerica');

以及GetMap程序

CREATE PROCEDURE GetMap(p_team VARCHAR(100), p_country VARCHAR(100))
BEGIN
SELECT Base FROM map WHERE 
(map.Base = p_team OR
Ver1 = p_team OR
Ver2 = p_team OR
Ver3 = p_team OR
Ver4 = p_team OR
Ver5 = p_team OR
Ver6 = p_team OR
Ver7 = p_team OR
Ver8 = p_team OR
Ver9 = p_team OR
Ver10 = p_team OR
Ver11 = p_team OR
Ver12 = p_team) AND Country = p_country;
end //
DELIMITER ;

Answer 1

不太确定您的查询出了什么问题。考虑到您为 map 表准备了适当的索引；您可以像下面这样修改您的查询。看起来覆盖索引会加速这个查询。

SELECT Base 
FROM map 
WHERE 
p_team IN (Ver1, Ver2, Ver3, Ver4, Ver5, Ver6, Ver7, Ver8, Ver9, Ver10, Ver11, Ver12, Base)
AND Country = p_country;