GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Profile query to find slowest join

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Profile query to find slowest join

    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="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.

    Thank you.

    Last edited by IlKa; 04-16-2013, 02:27 AM.

  • #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.

    Comment

    Working...
    X