PHP MYSQL timeout problem

Question

I have an SQL query like this. When iam executing it though my php file it gives time out error. But when running the same query thoruhg php myadmin it gives results within seconds.

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 

I know that increasing the time out may solve the problem. but i don't want to spend more than 30 sec for this query.

Answer 1

the problem is you haven't specific the relationship between your two tables. it returns quickly in phpmyadmin because phpmyadmin adds a LIMIT clause that allows the mysql server to stop sending rows quickly, never getting near the timeout.

you think the query is just retrieving the rows where the apartments are not rented and are active, but what you're really getting is that numbers of rows * the number of neighborhoods in your database.

rewrite your query like this:

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

note that i only guessed at how the two tables are related in the ON clause, so if i got it wrong you'll have to adjust it.

Answer 2

If you ONLY want rows where a match exists, your SQL needs to include an INNER JOIN. If cake_neighborhoods.cake_apartments_id is the name of your foreign key in the cake_neighborhoods table, I suggest rewriting the query as the following:

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