It is so surprised to me that MySQL Database does not support well in the use of subquery, which is the very basic syntax that we used in SQL.
These days, I received a performance report from my user and saying that one of our operations is very slow. A single SQL needs 1.5 minute to run on recent Dual Core server. After some investigations, I locked the target to one particular SQL during the execution of the operation. This SQL makes use of subquery which is in this syntax: WHERE…IN.
Google tells me that this issue has been reported since 2004 in early version of MySQL like 4.x. The problem was not focused in any of the updates afterward. After that, I read the article on the topic
How to optimize subqueries and joins in MySQL
This article not only shows the workaround to the issue, it also explains why the performance is so bad. It is briefly due to the reason that MySQL runs in an outside-inside manner, which means it first executes the main query and run the subquery later.
I am happy that there is a workaround to the issue, but I just don’t understand why MySQL does not fix the internal logic using this approach and need us, application developers, to do the workaround. Perhaps, that’s the problem of open-source project.
