zipkin query optimization

gistfile1.txt

-- original
SELECT DISTINCT
    `zipkin_spans`.`trace_id_high`,
    `zipkin_spans`.`trace_id`,
    MAX(`zipkin_spans`.`start_ts`)
FROM
    `zipkin_spans`
        JOIN
    `zipkin_annotations` ON (`zipkin_spans`.`trace_id_high` = `zipkin_annotations`.`trace_id_high`
        AND `zipkin_spans`.`trace_id` = `zipkin_annotations`.`trace_id`
        AND `zipkin_spans`.`id` = `zipkin_annotations`.`span_id`)
WHERE
    (`zipkin_spans`.`start_ts` BETWEEN 1501585320000000 AND 1503921750930000
        AND `zipkin_annotations`.`endpoint_service_name` = 'api')
GROUP BY `zipkin_spans`.`trace_id_high` , `zipkin_spans`.`trace_id`
ORDER BY MAX(`zipkin_spans`.`start_ts`) DESC
LIMIT 10

-- optimized
SELECT 
    zipkin_spans.*
FROM
    (SELECT 
        trace_id_high, trace_id, MAX(start_ts) AS duration
    FROM
        zipkin_spans
    WHERE
        start_ts BETWEEN 1501585320000000 AND 1503921750930000
    GROUP BY trace_id_high , trace_id) AS zipkin_spans
WHERE
    EXISTS( SELECT 
            1
        FROM
            zipkin_annotations
        WHERE
            `zipkin_spans`.`trace_id_high` = `zipkin_annotations`.`trace_id_high`
                AND `zipkin_spans`.`trace_id` = `zipkin_annotations`.`trace_id`
                AND endpoint_service_name = 'api')
ORDER BY duration DESC
LIMIT 10