MySQL optimizes subqueries

Question

I want to optimize this query (since subqueries are not fast in the general case), but I got lost because I cannot rewrite this with joins that would be better for performance, can you help mi with this?

SELECT id, company, street, number, number_addition, postalcode, telephone FROM clients WHERE (postalcode BETWEEN '1000' AND '9000') AND street = ( SELECT DISTINCT street FROM clients WHERE (postalcode BETWEEN '1000' AND '9000') AND postalcode <= ( SELECT MIN(postalcode) FROM clients WHERE street = 'Main Street' AND (postalcode BETWEEN '1000' AND '9000')) ORDER BY postalcode DESC LIMIT 1, 1) ORDER BY postalcode DESC, street DESC, number DESC, number_addition DESC, telephone DESC LIMIT 1 

Thanks for your time guys.

Answer 1

SELECT DISTINCT street ORDER BY postalcode does not make sense (and I think this is not ANSI SQL), if postalcode is independent of the street function, I don’t think it is like your get-low-postalcode-on-Main- Street would not make sense if that were the case. MySQL will allow you to avoid trouble, but the results will be inconsistent. What are you trying to say here?

I do not think this should be particularly slow, because what you have is not a dependent subquery; subqueries are executed only once and more than once for each outer row. You can rewrite it as three separate requests -

• Get the lower zip code on the main street;
• get a street with the second largest postcode below (1) (inconsistent);
• get information about customers on the street (2).

no difference in performance. (Indeed, it would be better to do this for clarity.)

You can rewrite them as joins using self-left-join-on-less-than-is-null to get min / max, but I don’t think you will get anything from this for this example and it will be very dirty, given the two levels of accession and the second largest requirement. In practice, is this a particularly slow question? What does EXPLAIN look like? Have you indexed postalcode and street ?