MySql Order by on a huge joined datasets is taking too long time.

Nisha_Suresh · May 4, 2015, 7:12am

We have done profiling in both the instance and got the result like below:
In Windows with MySQL5.5: [TABLE]
[TR]
[TD]Status[/TD]
[TD]Duration[/TD]
[TD]Source_function[/TD]
[TD]Source_file[/TD]
[TD]Source_line[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000018[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000008[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000015[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000043[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000024[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000007[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000006[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000022[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000022[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000007[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000007[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000007[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000007[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000013[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000023[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1791[/TD]
[/TR]
[TR]
[TD]Copying to tmp table[/TD]
[TD=“align: right”]0.000012[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]1938[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]0.000031[/TD]
[TD]JOIN::exec[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2349[/TD]
[/TR]
[TR]
[TD]end[/TD]
[TD=“align: right”]0.000003[/TD]
[TD]mysql_select[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]2585[/TD]
[/TR]
[TR]
[TD]removing tmp table[/TD]
[TD=“align: right”]0.000005[/TD]
[TD]free_tmp_table[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]10929[/TD]
[/TR]
[TR]
[TD]end[/TD]
[TD=“align: right”]0.000002[/TD]
[TD]free_tmp_table[/TD]
[TD].\sql_select.cc[/TD]
[TD=“align: right”]10954[/TD]
[/TR]
[TR]
[TD]query end[/TD]
[TD=“align: right”]0.000002[/TD]
[TD]mysql_execute_command[/TD]
[TD].\sql_parse.cc[/TD]
[TD=“align: right”]5132[/TD]
[/TR]
[TR]
[TD]freeing items[/TD]
[TD=“align: right”]0.000162[/TD]
[TD]mysql_parse[/TD]
[TD].\sql_parse.cc[/TD]
[TD=“align: right”]6156[/TD]
[/TR]
[TR]
[TD]logging slow query[/TD]
[TD=“align: right”]0.000001[/TD]
[TD]log_slow_statement[/TD]
[TD].\sql_parse.cc[/TD]
[TD=“align: right”]1744[/TD]
[/TR]
[TR]
[TD]cleaning up[/TD]
[TD=“align: right”]0.000005[/TD]
[TD]dispatch_command[/TD]
[TD].\sql_parse.cc[/TD]
[TD=“align: right”]1712[/TD]
[/TR]
[/TABLE]

Nisha_Suresh · May 4, 2015, 7:12am

In Linux with MySQL 5.6 server the result is: [TABLE]
[TR]
[TD]Status[/TD]
[TD]Duration[/TD]
[TD]Source_function[/TD]
[TD]Source_file[/TD]
[TD]Source_line[/TD]
[/TR]
[TR]
[TD]starting[/TD]
[TD=“align: right”]0.000237[/TD]
[TD]NULL[/TD]
[TD]NULL[/TD]
[TD]NULL[/TD]
[/TR]
[TR]
[TD]checking permissions[/TD]
[TD=“align: right”]0.00001[/TD]
[TD]check_access[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]5302[/TD]
[/TR]
[TR]
[TD]checking permissions[/TD]
[TD=“align: right”]0.000005[/TD]
[TD]check_access[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]5302[/TD]
[/TR]
[TR]
[TD]checking permissions[/TD]
[TD=“align: right”]0.000006[/TD]
[TD]check_access[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]5302[/TD]
[/TR]
[TR]
[TD]checking permissions[/TD]
[TD=“align: right”]0.000005[/TD]
[TD]check_access[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]5302[/TD]
[/TR]
[TR]
[TD]checking permissions[/TD]
[TD=“align: right”]0.000008[/TD]
[TD]check_access[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]5302[/TD]
[/TR]
[TR]
[TD]Opening tables[/TD]
[TD=“align: right”]0.000042[/TD]
[TD]open_tables[/TD]
[TD]sql_base.cc[/TD]
[TD=“align: right”]5017[/TD]
[/TR]
[TR]
[TD]init[/TD]
[TD=“align: right”]0.000164[/TD]
[TD]mysql_prepare_select[/TD]
[TD]sql_select.cc[/TD]
[TD=“align: right”]1050[/TD]
[/TR]
[TR]
[TD]System lock[/TD]
[TD=“align: right”]0.00002[/TD]
[TD]mysql_lock_tables[/TD]
[TD]lock.cc[/TD]
[TD=“align: right”]304[/TD]
[/TR]
[TR]
[TD]optimizing[/TD]
[TD=“align: right”]0.000067[/TD]
[TD]optimize[/TD]
[TD]sql_optimizer.cc[/TD]
[TD=“align: right”]138[/TD]
[/TR]
[TR]
[TD]statistics[/TD]
[TD=“align: right”]0.000572[/TD]
[TD]optimize[/TD]
[TD]sql_optimizer.cc[/TD]
[TD=“align: right”]362[/TD]
[/TR]
[TR]
[TD]preparing[/TD]
[TD=“align: right”]0.000085[/TD]
[TD]optimize[/TD]
[TD]sql_optimizer.cc[/TD]
[TD=“align: right”]485[/TD]
[/TR]
[TR]
[TD]Creating tmp table[/TD]
[TD=“align: right”]0.00005[/TD]
[TD]create_intermediate_table[/TD]
[TD]sql_executor.cc[/TD]
[TD=“align: right”]210[/TD]
[/TR]
[TR]
[TD]Sorting result[/TD]
[TD=“align: right”]0.000008[/TD]
[TD]make_tmp_tables_info[/TD]
[TD]sql_select.cc[/TD]
[TD=“align: right”]5248[/TD]
[/TR]
[TR]
[TD]executing[/TD]
[TD=“align: right”]0.000007[/TD]
[TD]exec[/TD]
[TD]sql_executor.cc[/TD]
[TD=“align: right”]110[/TD]
[/TR]
[TR]
[TD]Sending data[/TD]
[TD=“align: right”]155.054843[/TD]
[TD]exec[/TD]
[TD]sql_executor.cc[/TD]
[TD=“align: right”]190[/TD]
[/TR]
[TR]
[TD]Creating sort index[/TD]
[TD=“align: right”]0.801326[/TD]
[TD]sort_table[/TD]
[TD]sql_executor.cc[/TD]
[TD=“align: right”]2504[/TD]
[/TR]
[TR]
[TD]end[/TD]
[TD=“align: right”]0.000029[/TD]
[TD]mysql_execute_select[/TD]
[TD]sql_select.cc[/TD]
[TD=“align: right”]1105[/TD]
[/TR]
[TR]
[TD]removing tmp table[/TD]
[TD=“align: right”]0.00012[/TD]
[TD]free_tmp_table[/TD]
[TD]sql_tmp_table.cc[/TD]
[TD=“align: right”]1868[/TD]
[/TR]
[TR]
[TD]end[/TD]
[TD=“align: right”]0.000008[/TD]
[TD]free_tmp_table[/TD]
[TD]sql_tmp_table.cc[/TD]
[TD=“align: right”]1897[/TD]
[/TR]
[TR]
[TD]query end[/TD]
[TD=“align: right”]0.000009[/TD]
[TD]mysql_execute_command[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]5001[/TD]
[/TR]
[TR]
[TD]closing tables[/TD]
[TD=“align: right”]0.000028[/TD]
[TD]mysql_execute_command[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]5049[/TD]
[/TR]
[TR]
[TD]freeing items[/TD]
[TD=“align: right”]0.035736[/TD]
[TD]mysql_parse[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]6438[/TD]
[/TR]
[TR]
[TD]logging slow query[/TD]
[TD=“align: right”]0.000031[/TD]
[TD]log_slow_do[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]1879[/TD]
[/TR]
[TR]
[TD]cleaning up[/TD]
[TD=“align: right”]0.000028[/TD]
[TD]dispatch_command[/TD]
[TD]sql_parse.cc[/TD]
[TD=“align: right”]1783[/TD]
[/TR]
[/TABLE]

Both the instance has the same data. It seems like sending data is taking more time in Linux MySQL 5.6. Is the difference because of MySQL Versions or OS Difference.

Please help to resolve this.

scott.nemes · May 4, 2015, 10:10am

Hi Nisha;

My guess would be that your disks are a lot slower on the Linux 5.6 server. Aside from that, you basically are comparing apples and oranges, so that is difficult. You are comparing Linux vs Windows, 5.5 vs 5.6, and two entirely different servers of unknown configuration.

So I’d look for what the big server differences are, like disks/memory/CPU. You could also install 5.5 on the Linux server to see if it performs the same, or 5.6 on the Windows server. That will confirm if it’s the server itself, which is likely.

-Scott

Nisha_Suresh · May 5, 2015, 7:15am

Hi Scott,

Please find the system comparison as follows: [TABLE]
[TR]
[TD]Edition [/TD]
[TD]RAM[/TD]
[TD]System Type[/TD]
[TD]MySQL Version[/TD]
[/TR]
[TR]
[TD]Windows 7 Proffessional[/TD]
[TD]4 GB[/TD]
[TD]32 bit[/TD]
[TD]5.5.35[/TD]
[/TR]
[TR]
[TD]Fedora 2.0[/TD]
[TD]1.9 GiB[/TD]
[TD]64 Bit[/TD]
[TD]5.6.24[/TD]
[/TR]
[/TABLE]

One thing we noticed is in Linux Machine without order by the query is taking less than 3 secs and with order by the query is taking 145 secs.

While in profiling it says “sending data” is taking 144 secs

Nisha

scott.nemes · May 5, 2015, 11:06am

Hi Nisha;

Your Linux host is likely quite a bit underpowered compared to your Windows host. All you’ve given us so far is the memory, which is half the size on Linux. So the linux side probably has less CPU and and slower disks as well. So that is one issue. The other issue is that your Windows MySQL configuration is likely granting MySQL more resources to start with. So at very least you need to compare the configs and see what the differences are. Particularly your innodb_buffer_pool_size, sort_buffer_size, and read_rnd_buffer_size. The ORDER BY slows it down a lot because it is not optimize and likely has little resources to work with to do the manual processing needed.

Once you get the system problems sorted out, you need to determine what queries you are actually trying to optimize. I gave you some examples above, but you said the WHERE clause will change, and you cannot optimize a moving target like that. Identify what queries are slow and try to index them. Once they run faster, identify the next slow bunch, and so on. If you have many queries that have different WHERE clauses but share a few common fields in the WHERE clause, try optimizing those matching columns to give you the benefit over more query footprints.

-Scott

Topic		Replies	Views
ORDER BY ..LIMIT using indexes still slow Other MySQL® Questions	4	571	April 14, 2008
optimize help for order by Other MySQL® Questions	1	342	August 16, 2007
optimization for inner join.. order by.. limit with large order by data Other MySQL® Questions	4	590	January 13, 2010
Order by using an Inner Join not working in Linux environment of Mysql Other MySQL® Questions	1	643	April 23, 2015
Mysql not using index for ORDER BY ? Other MySQL® Questions	2	525	May 13, 2007

MySql Order by on a huge joined datasets is taking too long time.

Related topics