MySQL optimize subqueries

Question

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, 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.

Is this query really slow? Don't optimize at random. Enable slow_query_log in your MySQL server. This will log the queries that take more than long_query_time in your slow_query_log_file. — user699082
– user699082, Commented Oct 2, 2011 at 12:15

bobince · Accepted Answer · 2011-10-02 10:21:05Z

1

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—

get lowest postalcode on Main Street;
get street with second-highest postal code lower than (1) (inconsistently);
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?

answered Oct 2, 2011 at 10:21

bobince

538k111 gold badges675 silver badges846 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joseph Over a year ago

SELECT DISTINCT street ORDER BY postalcode - the thing is I have a table of clients and I want to get all streets in this table. Every streets can have multiple postalcodes so I want to choose the lowest value of postalcode and order the streets by this.

Joseph Over a year ago

I couldn't test this in production yet, but since this is a query that runs on the frontend of the app (rarely) I thought it would be a good idea to speed this up, but if you think it's OK than I'll wait how it will run in production. BTW both postalcode and street are indexed. Thanks for your time.

bobince Over a year ago

You're not ordering streets by minimum postcode, you're ordering by one random postcode per street. Other DBs will flag this as an error because it's usually a mistake. Try SELECT street ... GROUP BY street ORDER BY MIN(postcode).

Collectives™ on Stack Overflow

MySQL optimize subqueries

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related