CODEWITHSUNDEEP
mysqlsqlperformance
skiwi
skiwi

Reputation: 69269

MySQL performance of join combined with where clause

I currently got this query:

SELECT 
    location.street, 
    location.zip, 
    location.city, 
    surface, 
    price_buy, 
    price_rental 
FROM buildings 
JOIN location 
    ON building.location_id = location.location_id 
WHERE surface > 1000

The desired behaviour (in terms of performance) would be:

  1. Filter out all results from buildings with surface > 1000
  2. Retrieve the location data corresponding to the leftover buildings entries.

Is my query currently doing that? Syntactically I would expect that the WHERE would need to be before the JOIN, but is this even possible? Does MySQL itself does any optimisation, if so, does that same optimisation hold for all SQL languages?

Upvotes: 3

Views: 145

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29629

Firstly, your query is doing the opposite of what you want - it is filtering out (i.e. removing) buildings whose surface is <= 1000.

Syntactically I would expect that the WHERE would need to be before the JOIN, but is this even possible?

No, your syntax is fine. The reason the joins come first is because they identify the tables to query and there relationships; only after you've done that do you specify which rows you do and do not want.

The formality of SQL syntax allows the query optimizer to understand what you meant (as long as you

Does MySQL itself does any optimisation, if so, does that same optimisation hold for all SQL languages?

Yes, MySQL (and pretty much all SQL database engines) include a query optimizer, which translates your SQL into specific instructions. Notionally, SQL is a declarative language - you tell the computer what you want to achieve, and it is supposed to work out the most efficient way to do it - this is in contrast with imperative programming (PHP, C, Ruby etc.) where you tell the computer explicitly what to do, and in which order.

You can get a peak into the way this works with EXPLAIN.

From a performance point of view, the order of joins and where clauses is not supposed to make a difference (though I've seen some databases where this isn't always true); however, the indexing strategy for the tables has a huge impact. The bad news is that this does tend to vary between database engine.

Upvotes: 6

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

If you want the leftover buildings, make the WHERE clause opposite: surface <= 1000. This will return the non-fitting records.

Upvotes: 0

Related Questions