MySQL optimize subqueries

I want to optimize this query (since subqueries aren't fast in general), but I'm lost because I cannot rewrite this using joins which will be better for performance, can you help mi with this?

SELECT id, company, street, number, number_addition, postalc开发者_Python百科ode, 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.

SELECT DISTINCT street ORDER BY postalcode doesn't make sense (and I think isn't valid ANSI SQL), unless postalcode is functionally-dependent on street—which I don't think it is, as your get-lowest-postalcode-on-Main-Street inner subselect wouldn't make sense if it was. MySQL will let you get away with it but the results will be inconsistent. What are you trying to say here?

I don't think this should be particularly slow since what you have isn't a dependent subquery; the subqueries are executed only once and not repeatedly for each outer row. You could rewrite it as three separate queries—

1. get lowest postalcode on Main Street;
2. get street with second-highest postal code lower than (1) (inconsistently);
3. get details of clients on street (2).

with no difference in execution. (Indeed, it might be better to do so for clarity.)

You could rewrite these as joins, using self-left-joins-on-less-than-is-null to get the minima/maxima, but I don't think you'd gain anything by it for this example and it would get very messy given the two levels of joining and the second-highest requirement. Is this query particularly slow in practice? What does the EXPLAIN look like? Have you indexed postalcode and street?