PHP MYSQL超时问题

Question

我有一个这样的SQL查询。当我通过我的php文件执行它时，它给出了超时错误。但是，当运行同样的查询thoruhg php myadmin时，它会在几秒钟内给出结果。

SELECT
    cake_apartments.id,
    cake_apartments.Headline,
    cake_apartments.Description,
    cake_apartments.photoset_id,
    cake_apartments.Rental_Price,
    cake_apartments.Bedrooms,
    cake_apartments.Bathrooms
FROM
    cake_apartments,cake_neighborhoods
WHERE
    (cake_apartments.Rented = 0)
    AND
    (cake_apartments.status = 'Active')
ORDER BY
    cake_neighborhoods.Name DESC 

我知道增加超时可能会解决问题。但是我不想为这个查询花费超过30秒的时间。

Answer 1

问题是您还没有指定两个表之间的关系。它在phpmyadmin中快速返回，因为phpmyadmin添加了一个LIMIT子句，允许mysql服务器快速停止发送行，永远不会接近超时。

您认为查询只是检索公寓未出租且处于活动状态的行，但实际得到的是行数*数据库中的邻居区数。

重写查询，如下所示：

SELECT
    cake_apartments.id,
    cake_apartments.Headline,
    cake_apartments.Description,
    cake_apartments.photoset_id,
    cake_apartments.Rental_Price,
    cake_apartments.Bedrooms,
    cake_apartments.Bathrooms
FROM
    cake_apartments
JOIN
    cake_neighborhoods
  ON
    cake_neighborhoods.id = cake_apartments.neighborhood_id
WHERE
    (cake_apartments.Rented = 0)
    AND
    (cake_apartments.status = 'Active')
ORDER BY
    cake_neighborhoods.Name DESC

请注意，我只是在ON子句中猜测了这两个表是如何相关的，所以如果我弄错了，您必须对其进行调整。

Answer 2

如果只需要存在匹配的行，则SQL需要包含一个内部联接。如果cake_neighborhoods.cake_apartments_id是cake_neighborhoods表中的外键名称，我建议重写查询，如下所示：

SELECT
    cake_apartments.id,
    cake_apartments.Headline,
    cake_apartments.Description,
    cake_apartments.photoset_id,
    cake_apartments.Rental_Price,
    cake_apartments.Bedrooms,
    cake_apartments.Bathrooms,
    cake_neighborhoods.Name
FROM
    cake_apartments,cake_neighborhoods
WHERE
    (cake_apartments.Rented = 0)
    AND
    (cake_apartments.status = 'Active')
    AND
    cake_neighborhoods.cake_apartments_id = cake_apartments.id
ORDER BY
    cake_neighborhoods.Name DESC