如何构造更复杂的mysql查询以进行优化(How to construct a more complex mysql query for optimization)

 我有这个可爱的代码片段，我想优化： 
 
$result = mysql_query("select day from cities where name='St Charles'");
$row = mysql_fetch_assoc($result);
$day = $row['day'];

$result = mysql_query("select id,durability from goods_meta_data");

while($row = mysql_fetch_assoc($result)) {
    mysql_query("delete from possessions where day_created<" . ($day-$row[durability]) . " and good_id=" . $row['id']);
}
 
 如果占有已过期，它会从表'所有'中删除行。 占有权是否已过期取决于表格“商品”和“城市”中的值。 具体来说，商品的年龄= day-day_created，如果商品的年龄超过其耐久性，那么它就会过期。 （每一种财产都是一种商品，每种商品都具有独特的耐久性。） 
 
 这段代码有效，但我觉得这可以在一个查询中完成。 mysql服务器的延迟特别大，因此在较少的查询中执行此操作将非常有益。 
 
 我怎么做？ 有任何想法吗？ 
 
 另外，如果您能指出我可以学习如何以这种方式更好地利用关系数据库的任何有用资源，那将会很有帮助。 

I have this lovely piece of code that I want to optimize:
 
$result = mysql_query("select day from cities where name='St Charles'");
$row = mysql_fetch_assoc($result);
$day = $row['day'];

$result = mysql_query("select id,durability from goods_meta_data");

while($row = mysql_fetch_assoc($result)) {
    mysql_query("delete from possessions where day_created<" . ($day-$row[durability]) . " and good_id=" . $row['id']);
}
 
It deletes rows from the table 'possessions' if the possession has expired. Whether or not the possession has expired is determined by values in the tables 'goods' and 'cities'. Specifically, the age of the good = day-day_created, if the age of the good is more than its durability, then it is expired. (Every possession is a type of good, and every good has a unique durability.)
 
This code works, but I feel like this could be done in a single query. The latency to the mysql server is particularly large, so doing this operation in less queries would be very beneficial.
 
How do I do that? Any ideas?
 
Also, if you can point me to any useful resources where I can learn about taking better advantage of relational databases in this manner, it would be helpful.

原文：https://stackoverflow.com/questions/17644838