ANALYZE FORMAT=JSON shows r_total_time_ms field which allows one to see where the time was spent during execution.
r_total_time_ms is provided for
- query_block, aka a SELECT
- table, individual table references
- filesort node.
The issue is that for many-table join one can frequently see that
- The join itself (query_block) took a lot of time
- table access in the join add up to a fraction of time.
this leaves one puzzled re where the time is spent.
An example of such case is