SQL Query taking too long

Maximus_Collins

I'm looking to speed this query up. It takes about 8 seconds as is.

$query = "SELECT name, abbrev
FROM State
WHERE abbrev IN (SELECT DISTINCT state FROM Unit WHERE
unitId IN (SELECT unitId FROM Postings WHERE active = 'y' and expires >= now()))

        ORDER BY name";

State table has 50 rows, Unit table has 600 or so rows, Postings has 600 or so rows.

Any ideas?

Piranha

Strange that it is that slow, are you sure it is the query and not in another place of the code? Use [man]microtime[/man] to be able to see what takes time.

paulnaj

Hi,

It seems that you're looking for a list of distinct States that occur in Postings that satisfy the WHERE condition (I could be wrong about this 😉).

If so, you could use JOINS and GROUP BY State which I imagine might take less time ...

SELECT State.name, State.abbrev

FROM State
LEFT JOIN Unit ON (Unit.state = State.abbrev)
LEFT JOIN Postings ON (Postings.unitId = Unit.unitId)

WHERE Postings.active = 'y' 
  AND Postings.expires >= now()

GROUP BY State.abbrev
ORDER BY State.name

PS. You could at least test the speed of this query and compare it with the original .

Maximus_Collins

Thank you for the tips.

The join approach worked very well: 0.0746231079102 seconds
vs.
My version at: 11.6517767906 seconds.

Well done!