包含多个部分的大型脚本...我如何优化这一点？
包含多个部分的大型脚本...我如何优化这一点？
提问于 2012-09-29 00:05:45
我有一个脚本，它从一个表接收信息，确定它是否需要为另一个表创建一个条目(避免重复)，然后将信息插入到pages表中。问题是，按照目前的编码方式查询大约10,000个条目，会导致性能问题。
我可以用什么方法来优化它，以使其运行得更高效、更快？正如您从代码中看到的，这有许多部分。
$eventquery = mysql_query("SELECT * FROM maintable WHERE PCatID != '3' AND PCatID != '4'");
while($event = mysql_fetch_array($eventquery))
{
$querystring = null;
$performer_id = array();
$hasharray = array();
$venue = preg_replace("/ \([fF]ormerly (.+?)\)/", '', $event['Venue']);
$perfquery = mysql_query("SELECT * FROM maintable WHERE EventID = '".$event['EventID']."'");
while($perf = mysql_fetch_array($perfquery))
{
$performer_id[$perf['PerformerID']] = $perf['Performer'];
$hasharray[] = $perf['id'];
}
$eventtime = getEventTime($event['DateTime']);
$performer_id = serialize($performer_id);
$hasharray = serialize($hasharray);
if($event['PCatID'] == "2" && $event['CCatID'] != "24")
{
$request = "buy-".strCleanupForPage($event['Event'])."-concert-tickets/".strCleanupForPage($event['City'])."-".
strCleanupForPage(stateabb($event['State']))."-".strCleanupForPage($venue)."-".$eventtime['date'];
$catnum = "202";
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray, mainpage) VALUES
('".$request."','".$catnum."','".addslashes($event['Event'])."','".addslashes($performer_id)."',
'".$event['EventID']."','".$hasharray."','n')";
}
elseif($event['GCatID'] == "32" || $event['GCatID'] == "16" || $event['GCatID'] == "30" || $event['GCatID'] == "19" ||
$event['GCatID'] == "31" || $event['GCatID'] == "22")
{
if($event['GCatID'] == "32")
{$catnum = "102";}
elseif($event['GCatID'] == "16")
{$catnum = "101";}
elseif($event['GCatID'] == "30")
{$catnum = "103";}
elseif($event['GCatID'] == "19")
{$catnum = "104";}
elseif($event['GCatID'] == "31")
{$catnum = "109";}
elseif($event['GCatID'] == "22")
{$catnum = "108";}
elseif($event['GCatID'] == "17")
{
if($event['CCatID'] == "66")
{$catnum = "106";}
elseif($event['CCatID'] == "65")
{$catnum = "105";}
}
if(strstr($event['Event']," vs. "))
{
$game = true;
$teams = explode(" vs. ",$event['Event']);
}
elseif(strstr($event['Event']," Vs. "))
{
$game = true;
$teams = explode(" Vs. ",$event['Event']);
}
else
{
$game = false;
}
if($game)
{
$catnum .= "2";
$homequery = mysql_query("SELECT * FROM sportsteams WHERE fullname LIKE '".$teams[0]."'");
$home = mysql_fetch_array($homequery);
$awayquery = mysql_query("SELECT * FROM sportsteams WHERE fullname LIKE '".$teams[1]."'");
$away = mysql_fetch_array($awayquery);
if((mysql_num_rows($homequery)) == 0 || (mysql_num_rows($awayquery)) == 0)
{
if($catnum == "1012")
{$request = "mlb-baseball-tickets";}
elseif($catnum == "1022")
{$request = "nfl-football-tickets";}
elseif($catnum == "1032")
{$request = "nba-basketball-tickets";}
elseif($catnum == "1042")
{$request = "nhl-hockey-tickets";}
elseif($catnum == "1052")
{$request = "ncaa-football-tickets";}
elseif($catnum == "1062")
{$request = "ncaa-basketball-tickets";}
elseif($catnum == "1082")
{$request = "mls-soccer-tickets";}
elseif($catnum == "1092")
{$request = "wnba-basketball-tickets";}
$eventname = preg_replace("/\([iI]ncludes (.+?)\)/", '', $event['Event']);
$request .= "/".strCleanupForPage($eventname)."-".strCleanupForPage($event['City'])."-".
strCleanupForPage(stateabb($event['State']));
}
else
{
$request = strCleanupForPage($home['fullname'])."-tickets-".strCleanupForPage($venue)."/".strCleanupForPage($home['nickname'])."-vs-".strCleanupForPage($away['nickname'])."-".strCleanupForPage($event['City'])."-".
strCleanupForPage(stateabb($event['State']))."-".$eventtime['date'];
$request = str_replace("-/","/",$request);
}
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray, mainpage) VALUES
('".$request."','".$catnum."','".addslashes($event['Event'])."','".addslashes($performer_id)."',
'".$event['EventID']."','".$hasharray."','n')";
}
else
{
$catnum .= "3";
if($catnum == "1013")
{$request = "mlb-baseball";}
elseif($catnum == "1023")
{$request = "nfl-football";}
elseif($catnum == "1033")
{$request = "nba-basketball";}
elseif($catnum == "1043")
{$request = "nhl-hockey";}
elseif($catnum == "1053")
{$request = "ncaa-football";}
elseif($catnum == "1063")
{$request = "ncaa-basketball";}
elseif($catnum == "1083")
{$request = "mls-soccer";}
elseif($catnum == "1093")
{$request = "wnba-basketball";}
$eventname = preg_replace("/\([iI]ncludes (.+?)\)/", '', $event['Event']);
$request .= "/".strCleanupForPage($eventname)."-".strCleanupForPage($event['City'])."-".
strCleanupForPage(stateabb($event['State']));
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray, mainpage) VALUES
('".$request."','".$catnum."','".addslashes($event['Event'])."','".addslashes($performer_id)."',
'".$event['EventID']."','".$hasharray."','n')";
}
}
elseif($event['CCatID'] == "50")
{
$request = "boxing-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
('".$request."','1142','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
'".$hasharray."')";
}
elseif($event['CCatID'] == "67")
{
$request = "golf-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
('".$request."','1112','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
'".$hasharray."')";
}
elseif($event['CCatID'] == "27")
{
$request = "tennis-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
('".$request."','1102','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
'".$hasharray."')";
}
elseif($event['CCatID'] == "101")
{
$request = "ufc-mma-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
('".$request."','1152','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
'".$hasharray."')";
}
elseif($event['CCatID'] == "69")
{
if(strstr($event['Event'],"NASCAR"))
{
$request = "nascar-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
('".$request."','1122','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
'".$hasharray."')";
}
elseif($event['GCatID'] == "35")
{
$request = "horse-racing-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
('".$request."','1132','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
'".$hasharray."')";
}
}
elseif($event['GCatID'] == "26")
{
$request = "wwe-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
('".$request."','1162','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
'".$hasharray."')";
}
elseif($event['CCatID'] == "24")
{
$catnum = "402";
$request = strCleanupForPage($event['Event'])."-comedy-tickets/".strCleanupForPage($event['City'])."-".strCleanupForPage(stateabb($event['State'])).
"-".strCleanupForPage($event['Venue'])."-".$eventtime['date'];
$querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray, mainpage) VALUES
('".$request."','".$catnum."','".addslashes($event['Event'])."','".addslashes($performer_id)."',
'".$event['EventID']."','".$hasharray."','n')";
}
if($querystring)
{
$request = str_replace("--","-",$request);
if(!mysql_query($querystring))
{
exit("<br>" . mysql_error());
}
else
{
if(mysql_affected_rows() != 0)
{
echo date('H:i:s') . ": ";
echo $request . " entry inserted<br>";
}
else
{
//echo "<i>" .$request . " entry ignored</i><br>";
}
}
}
}任何帮助都是非常感谢的。提前谢谢你！
Stack Overflow用户
发布于 2012-09-29 00:32:40
你的数据库结构看起来非常奇怪，但是我可能错了，因为你的代码读起来相当可怕。
一个问题是它没有被规范化。对于maintable，你必须为1个事件做几次选择。那么，您的主表应该首先更改名称，然后只保存事件和仅与事件直接相关的数据。
对于参与的表演者或团队，您需要查询另一个表(可能包含此脚本的联接)，该表将事件id与表演者数据一起保存。
您的类别ID也应该在数据库中，并且还可以在第一个查询中联接。任何使处理方式不同的类别属性也将存储在此表中。
你的问题不是你有10k行，而是数据库结构。
阅读有关数据库规范化的知识，尤其是第三范式。然后给它一个很好的尝试，并随时回来询问结果。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12643507
复制相关文章
相似问题
腾讯云开发者
