Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As Aaron posted, MySQL optimizer is sometimes plain dumb. If you are not working with MariaDB latest versions (and you can't wait for MySQL 5.6 to be released), you can still "fool" the optimizer into running the subquery once, by moving it from the WHERE to the FROM clause: