5.7.1.11. Filesort Probes

The filesort probes are triggered whenever a filesort operation is applied to a table. For more information on filesort and the conditions under which it occurs, see Section 8.13.13, "ORDER BY Optimization".

filesort-start(database, table)filesort-done(status, rows)

filesort-start: Triggered when the filesort operation starts on a table. The two arguments to the probe, database and table, will identify the table being sorted.
filesort-done: Triggered when the filesort operation completes. Two arguments are supplied, the status (0 for success, 1 for failure), and the number of rows sorted during the filesort process.

An example of this is in the following script, which tracks the duration of the filesort process in addition to the duration of the main query:

#!/usr/sbin/dtrace -s#pragma D option quietdtrace:::BEGIN{   printf("%-2s %-10s %-10s %9s %18s %-s \n",          "St", "Who", "DB", "ConnID", "Dur microsec", "Query");}mysql*:::query-start{   self->query = copyinstr(arg0);   self->who   = strjoin(copyinstr(arg3),strjoin("@",copyinstr(arg4)));   self->db    = copyinstr(arg2);   self->connid = arg1;   self->querystart = timestamp;   self->filesort = 0;   self->fsdb = "";   self->fstable = "";}mysql*:::filesort-start{  self->filesort = timestamp;  self->fsdb = copyinstr(arg0);  self->fstable = copyinstr(arg1);}mysql*:::filesort-done{   this->elapsed = (timestamp - self->filesort) /1000;   printf("%2d %-10s %-10s %9d %18d Filesort on %s\n",          arg0, self->who, self->fsdb,          self->connid, this->elapsed, self->fstable);}mysql*:::query-done{   this->elapsed = (timestamp - self->querystart) /1000;   printf("%2d %-10s %-10s %9d %18d %s\n",          arg0, self->who, self->db,          self->connid, this->elapsed, self->query);}

Executing a query on a large table with an ORDER BY clause that triggers a filesort, and then creating an index on the table and then repeating the same query, you can see the difference in execution speed:

St Who        DB            ConnID       Dur microsec Query 0 @localhost test              14           11335469 Filesort on t1 0 @localhost test              14           11335787 select * from t1 order by i limit 100 0 @localhost test              14          466734378 create index t1a on t1 (i)0 @localhost test              14              26472 select * from t1 order by i limit 100