Wrong database query design guidelines

One of the guidelines of querying database is to do the max you can in 1 query and not splitting it, Because even if it would result in few joins, the query optimizer will optimize that off.

Then I noticed a query resulting 1 row was taking 1.5 sec and I checked why is that.

The query was like

SELECT a.b FROM a, c WHERE a.d = c.e AND c.f = 'g'

While I knew c.f = 'g' matches one row, I thought that as the guidelines say, the query optimizer will catch it. Well, it didn't.

Splitting this to two queries which first one retrieves c.e and second one uses that to retrieve a.b reduced time to few milliseconds. I have a.d foreign key 开发者_如何学编程of c.e and, mentioned columns except a.b uniquely indexed, both tables have about 2 mil rows and I use MySQL with InnoDB.

Is it really a problem with the guidelines or I understood something wrong?

• Also consider whether INDEXE's are available on a.d, c.e, c.f
• Use the query profiler to further refine your queries based on your particular requirements: http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

And also remember that you can improve performance in other ways too like query cache, OS tuning etc: http://forge.mysql.com/wiki/Top10SQLPerformanceTips