opttrace

Tools for processing MySQL optimizer trace

This project is for tools/scripts that can be used to extract information from Optimizer Trace generated by the MySQL Query Optimizer.

Currently, it contains one script, joinopttrace.js, which produces a condensed summary of the join optimizer. As the suffix indicates, it is written in Javascript, and if you have Node.js installed, it can be run like this:

node joinopttrace.js _tracefile_

Example output:

select#1:
Table AccessType:IndexName Rows/Cost TotalRows/TotalCost
--------------------------------------------------------
`customer` scan 2367/244.2 478.9/244.2
  `lineitem` scan 2495/119491 1190000/119735
    `orders` eq_ref:PRIMARY 1/120108 59742/239843 *** NEW BEST PLAN ***
  `orders` scan 2502/101621 478.9/101865
    `lineitem` ref:PRIMARY 1.0155/168.35 486.3/102033 *** NEW BEST PLAN ***
`lineitem` scan 2495/255.5 2495/255.5
  `customer` scan 2367/119716 1190000/119972 PRUNED(cost)
  `orders` eq_ref:PRIMARY 1/873.25 2115.1/1128.8
    `customer` eq_ref:PRIMARY 1/740.27 427.92/1869 *** NEW BEST PLAN ***
`orders` scan 2502/255.7 2121/255.7
  `customer` eq_ref:PRIMARY 1/742.35 429.12/998.05
    `lineitem` ref:PRIMARY 1.0155/150.86 435.76/1148.9 *** NEW BEST PLAN ***
  `lineitem` ref:PRIMARY 1.0155/745.63 2153.8/1001.3 PRUNED(heuristic)

Also included is a framework for regression testing. A bash script, opttracetest will process the traces stored in the trace_examples subdirectory and compare the output with pre-recorded results.

Usage:

opttracetest [-r] [tracefile ...]

-r records the current results

If -r is not specified, and there is no existing result file, the current result will be printed to stdout.

If no tracefile is specified, all files in the trace_examples directory will be processed.

If the environment variable MYSQL_SOURCE is set to an existing MySQL source tree, all traces of the MySQL opt_trace test suite will also be processed.