PHP:查询超过最大时间(如何处理)

Question

好的。我终于为我的 php 文件编写了查询(它将查询写入 txt 文件)。但是，当我早些时候测试文件时，我显然超出了最大执行时间。我只使用一个大型查询来完成这项工作，但现在我想知道是否最好使用多个较小的查询。 这会减少执行时间吗？

如果可以，那么我还有一个问题要问大家，我将如何解决这个问题？这是我的大部分文件:

// Query the database for data
$query = "SELECT 
  cards.card_id, 
  concat(cards.title, ' By Amy') AS cardTitle, 
  cards.meta_description,
  cards.description, 
  '' as Weight,
  '' as UPC,
  cards.seo_keywords,
  concat('http://www.amyadele.com/attachments//cards/',cards.card_id,'/',cards.card_image) AS ImageURL,
  card_import.artist,
  concat('ARTIST - ', card_import.artist) AS Brand,
  min(card_lookup_values.card_price) AS LowPrice,
 replace(lower(concat( 'http://www.amyadele.com/', pcat.seoname,'/',cat.seoname, '/', cards.seoname, '.htm' )),' ','+') AS link,
            concat(pcat.name,'>',cat.name) as Merchant,
 round(min(card_lookup_values.card_price), 2) AS newPrice,
 min(cast(lookup_details.value as signed)) as quantity

FROM
  cards
INNER JOIN card_cheapest on cards.card_id = card_cheapest.card_id
LEFT JOIN card_import on card_import.card_id = cards.card_id
INNER JOIN card_lookup_values on card_lookup_values.card_id = cards.card_id
INNER JOIN card_categories cc ON cards.card_id = cc.card_id AND cards.card_live = 'y' AND cards.active = 'y' AND cc.active = 'Y'
INNER JOIN categories cat ON cat.category_id = cc.category_id AND cat.active = 'Y'
INNER JOIN categories pcat ON cat.parent_category_id = pcat.category_id
INNER JOIN card_lookup_values as card_values ON cards.card_id = card_values.card_id
INNER JOIN lookup_details ON card_lookup_values.lookup_detail_id = lookup_details.lookup_detail_id

WHERE card_lookup_values.lookup_id = 7
GROUP BY
  cards.card_id
ORDER BY
  cards.card_id";


$result = mysql_query($query);


// Open file for writing
$myFile = "google.txt";
$fh = fopen($myFile, 'w') or die("can't open file");

// Loop through returned data and write (append) directly to file
fprintf($fh, "%-25s %-200 \n", "ID", "Name");

fprintf($fh, "\n");
while ($row = mysql_fetch_assoc($result)) {

 fprintf($fh, "%-25s %-200s \n", $row['card_id'], $row['cardTitle']);

 }

// Close out the file
fclose($fh);

echo "The file has been written sucessfully to googleproducts.txt. It will run again tomorrow at 12:00pm."
?>

如您所见，我将 $query 拉入 $results，然后使用它将其插入 $row。 我想我的实际问题(如果可能的话)是我如何使用 $query 和 $query2 并将 $query2 插入同一个 while 语句？

Answer 1

多个查询，如果有的话，只会减慢速度。这意味着编译/执行多个查询，所以你最终会有更多的开销。在查询中使用 explain 来查看正在使用(和未使用)哪些索引，并在需要时添加更多索引。在某些情况下，事情自然会变慢。毕竟，您是在 7 个表上进行 8 向连接。

检查索引使用情况的明显位置:where 和 join 子句中的每个字段。