My query takes ~ 50 seconds. When all data is cached in InnoDb pool it takes 2 sec, but I speak about situation when pool is empty.
Explain shows nothing special: ~ 10 tables (there are a lot of joins) with several rows eq_ref or range.
So, I tried to use profiler.
Profiler told me MySQL spent ~ 80% of time in “Sending data (sql_select.cc, line 2347)” state. That is not network output anyway because after all I select about 10 varchar columns in 5 rows.
According to sources ([URL=“https://code.google.com/p/google-mysql/source/browse/sql/sql_select.cc?spec=svnb94bc970570862bce5484bbfbf81"]https://code.google.com/p/google-mys...bce5484bbfbf81[/URL="https://code.google.com/p/google-mysql/source/browse/sql/sql_select.cc?spec=svnb94bc970570862bce5484bbfbf81”] 0c57589c0ad3&r=b94bc970570862bce5484bbfbf810c57589 c0ad3) it runs " do_select".
This function not always send data to user, so state name is bad and community knows it: [URL=“http://bugs.mysql.com/bug.php?id=63375"]http://bugs.mysql.com/bug.php?id=63375[/URL="http://bugs.mysql.com/bug.php?id=63375”]
But in my case “do_select” calls “sub_select” (*table is NULL), and I am pretty sure my 80% is there.
But there no any profile code in this function.
My question is: how can I find which join is bottleneck?
Microsoft has “cost” feature ([URL=“http://www.pinaldave.com/download/SQLSERVER_EXCEPT_ExecPlan_Big.gif"]http://www.pinaldave.com/download/SQLSERVER_EXCEPT_ExecPlan_Big.gif[/URL="http://www.pinaldave.com/download/SQLSERVER_EXCEPT_ExecPlan_Big.gif”])
What is MySQL analogue?
My first idea was to comment this bug and ask them to separate each loop into “state” (so I can find it by “show profile”), but I am not sure if it is against architecture.