Profile query to find slowest join

MySQL & MariaDB Other MySQL® Questions
1

Hello,

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=“Google Code Archive - Long-term storage for Google Code Project Hosting.”]Google Code Archive - Long-term storage for Google Code Project Hosting. 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=“MySQL Bugs: #63375: Change the state from "Sending data" when running do_select”]MySQL Bugs: #63375: Change the state from "Sending data" when running do_select
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.

Thank you.

2

You may omit tables from your query and see how you can speed up the query. If you read about indices and about explain output, you will know that range is much worse than eq_ref. You can also see a large number of rows in the rows column, even though that is only an indication.