database - MySQL show used index in query -
for example have created 3 index:
click_date-transactiontable,daily_metrictableorder_date-transactiontable
i want check query use index, use explain function , result:
+----+--------------+--------------+-------+---------------+------------+---------+------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+--------------+--------------+-------+---------------+------------+---------+------+--------+----------------------------------------------+ | 1 | primary | <derived2> | | null | null | null | null | 668 | using temporary; using filesort | | 2 | derived | <derived3> | | null | null | null | null | 645 | | | 2 | derived | <derived4> | | null | null | null | null | 495 | | | 4 | derived | transaction | | order_date | null | null | null | 291257 | using where; using temporary; using filesort | | 3 | derived | daily_metric | range | click_date | click_date | 3 | null | 812188 | using where; using temporary; using filesort | | 5 | union | <derived7> | | null | null | null | null | 495 | | | 5 | union | <derived6> | | null | null | null | null | 645 | using where; not exists | | 7 | derived | transaction | | order_date | null | null | null | 291257 | using where; using temporary; using filesort | | 6 | derived | daily_metric | range | click_date | click_date | 3 | null | 812188 | using where; using temporary; using filesort | | null | union result | <union2,5> | | null | null | null | null | null | | +----+--------------+--------------+-------+---------------+------------+---------+------+--------+----------------------------------------------+ in explain results see, index order_date of transaction table not used, correct understand ? index click_date of daily_metric table used correct ?
please tell how understand explain result created index used in query ?
my query:
select partner_id, the_date, sum(clicks) clicks, sum(total_count) total_count, sum(count) count, sum(total_sum) total_sum, sum(received_sum) received_sum, sum(partner_fee) partner_fee ( select clicks.partner_id, clicks.click_date the_date, clicks, orders.total_count, orders.count, orders.total_sum, orders.received_sum, orders.partner_fee (select partner_id, click_date, sum(clicks) clicks daily_metric date(click_date) between '2013-04-01' , '2013-04-30' group partner_id , click_date) clicks left join (select partner_id, date(order_date) order_dates, sum(order_sum) total_sum, sum(customer_paid_sum) received_sum, sum(partner_fee) partner_fee, count(*) total_count, count(case when status = 1 1 else null end) count transaction date(order_date) between '2013-04-01' , '2013-04-30' group date(order_date) , partner_id) orders on orders.partner_id = clicks.partner_id , clicks.click_date = orders.order_dates union select orders.partner_id, orders.order_dates the_date, clicks, orders.total_count, orders.count, orders.total_sum, orders.received_sum, orders.partner_fee (select partner_id, click_date, sum(clicks) clicks daily_metric date(click_date) between '2013-04-01' , '2013-04-30' group partner_id , click_date) clicks right join (select partner_id, date(order_date) order_dates, sum(order_sum) total_sum, sum(customer_paid_sum) received_sum, sum(partner_fee) partner_fee, count(*) total_count, count(case when status = 1 1 else null end) count transaction date(order_date) between '2013-04-01' , '2013-04-30' group date(order_date) , partner_id) orders on orders.partner_id = clicks.partner_id , clicks.click_date = orders.order_dates clicks.partner_id null order the_date desc ) t group the_date order the_date desc limit 50 offset 0
although can't explain explain has dumped, thought there must easier solution have , came following. suggest following indexes optimize existing query date range , grouping partner.
additionally, when have query uses function on field, doesn't take advantage of index. such date(order_date) , date(click_date). allow index better used, qualify full date/time such 12:00am (morning) 11:59pm. typically via
x >= somedate @12:00 , x < firstdayafterrange. in example (notice less may 1st gets april 30th @ 11:59:59pm)
click_date >= '2013-04-01' , click_date < '2013-05-01' table index transaction (order_date, partner_id) daily_metric (click_date, partner_id) now, adjustment. since clicks table may have entries transactions dont, , vice-versa, adjust query pre-query of possible date/partners, left-join respective aggregate queries such as:
select allparnters.partner_id, allparnters.the_date, coalesce( clicks.clicks, 0 ) clicks, coalesce( orders.total_count, 0 ) totalcount, coalesce( orders.count, 0 ) ordercount, coalesce( orders.total_sum, 0 ) ordersum, coalesce( orders.received_sum, 0 ) receivedsum, coalesce( orders.partner_fee 0 ) partnerfee ( select distinct dm.partner_id, date( dm.click_date ) the_date daily_metric dm dm.click_date >= '2013-04-01' , dm.click_date < '2013-05-01' union select t.partner_id, date(t.order_date) the_date transaction t t.order_date >= '2013-04-01' , t.order_date < '2013-05-01' ) allparnters left join ( select dm.partner_id, date( dm.click_date ) sumdate, sum( dm.clicks) clicks daily_metric dm dm.click_date >= '2013-04-01' , dm.click_date < '2013-05-01' group dm.partner_id, date( dm.click_date ) ) clicks on allpartners.partner_id = clicks.partner_id , allpartners.the_date = clicks.sumdate left join ( select t.partner_id, date(t.order_date) sumdate, sum(t.order_sum) total_sum, sum(t.customer_paid_sum) received_sum, sum(t.partner_fee) partner_fee, count(*) total_count, count(case when t.status = 1 1 else null end) count transaction t t.order_date >= '2013-04-01' , t.order_date < '2013-05-01' group t.partner_id, date(t.order_date) ) orders on allpartners.partner_id = orders.partner_id , allpartners.the_date = orders.sumdate order allpartners.the_date desc limit 50 offset 0 this way, first query quick on index possible combinations either table. left-join @ join 1 row per set. if found, number, if not, applying coalesce() if null, defaults zero.
clarification.
like when building pre-aggregate queries of "clicks" , "orders", "allpartners" alias result of select distinct of partners , dates within date range interested in. resulting columns of "partner_id" , "the_date" respective next queries. basis of joining aggregates of "clicks" , "orders". so, since have these 2 columns in alias "allparnters", grabbed field list since left-joined other aliases , may not exist in either/or respective others.
Comments
Post a Comment