Skip to content

Instantly share code, notes, and snippets.

@mustmodify

mustmodify/explain

Created August 15, 2019 14:59
Show Gist options
  • Save mustmodify/3883495057a5988da2ecd19d2c5296ca to your computer and use it in GitHub Desktop.
Save mustmodify/3883495057a5988da2ecd19d2c5296ca to your computer and use it in GitHub Desktop.
Download ZIP
Raw
explain
+----+-------------+----------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+---------------------------------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+---------------------------------------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | service_orders | NULL | range | PRIMARY,index_service_orders_on_number_and_episode,index_service_orders_on_purpose,index_service_orders_on_client_id,index_service_orders_on_zone_id,index_service_orders_on_customer_id,index_service_orders_on_created_at,index_service_orders_on_original_service_order_id,index_service_orders_on_hold_until_and_cancellation_id,index_service_orders_on_bill_strategy,index_service_orders_on_progenitor_type_and_progenitor_id,slow_so_filters,slow_so_filters_2,index_service_orders_on_available_at_and_hold_until | PRIMARY | 4 | NULL | 649 | 10.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | customers | NULL | eq_ref | PRIMARY,index_customers_on_location_id | PRIMARY | 4 | saw_dev_v3.service_orders.customer_id | 1 | 100.00 | Using where |
| 1 | SIMPLE | locations | NULL | eq_ref | PRIMARY | PRIMARY | 4 | saw_dev_v3.customers.location_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | zipcodes | NULL | ref | index_zipcodes_on_code | index_zipcodes_on_code | 768 | saw_dev_v3.locations.zipcode | 1 | 100.00 | NULL |
| 1 | SIMPLE | phone_numbers | NULL | ref | index_phone_numbers_on_phoneable_type_and_phoneable_id | index_phone_numbers_on_phoneable_type_and_phoneable_id | 128 | const,saw_dev_v3.service_orders.customer_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | auto_calls | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+---------------------------------------------+------+----------+----------------------------------------------------+
Raw
query
explain SELECT
service_orders.id as service_order_id,
CONCAT(CONCAT(customers.first_name, " "), customers.last_name) as customer,
locations.zipcode as zip,
CONVERT_TZ(UTC_TIME(), "UTC", zipcodes.time_zone) as local_time,
COUNT(phone_numbers.id) AS numbers,
COUNT(auto_calls.id) AS auto_calls_today,
MAX(auto_calls.created_at) as last_autocall_provisioned_at,
OUP_CONCAT(auto_calls.outcome) as outcomes
FROM service_orders
INNER JOIN `customers`ON `customers`.`id` = `service_orders`.`customer_id`
LEFT OUTER JOIN phone_numbers ON phone_numbers.phoneable_id = customers.id
AND phone_numbers.phoneable_type = "Customer"
INNER JOIN `locations` ON `locations`.`id` = `customers`.`location_id`
LEFT OUTER JOIN `zipcodes` ON `zipcodes`.`code` = `locations`.`zipcode`
LEFT OUTER JOIN auto_calls ON auto_calls.reference_type = "ServiceOrder"
AND auto_calls.reference_id = service_orders.id AND DATE(CONVERT_TZ(auto_calls.created_at, "UTC", "US/Eastern")) = CURDATE()'
AND auto_calls.destination_number = phone_numbers.number
WHERE `service_orders`.`id` IN (807538,806862,797361,807537,807496,807361,807157,807154,807152,807135,806959,806768,806604,806257,806253,806097,805459,805008,801935,798898,798890,807532,807528,807526,807525,807518,807513,807507,807468,807464,807451,807389,807339,807251,807227,807220,807207,807195,807191,807179,807169,807165,807160,807108,807068,807016,806996,806981,806955,806922,806819,806796,806786,806780,806727,806694,806692,806332,806323,806265,806263,806212,806185,806183,806126,806121,806110,805993,805966,805961,805944,805900,805661,805517,805504,805498,805144,805065,804619,804618,804608,804360,804338,804318,804014,802895,802886,802853,802675,801900,799581,799452,807585,807583,807574,807573,807568,807562,807560,807558,807556,807554,807550,807546,807541,807539,807535,807534,807529,807527,807521,807520,807519,807516,807515,807514,807512,807510,807509,807506,807505,807504,807503,807502,807501,807500,807499,807498,807497,807495,807494,807492,807491,807489,807487,807486,807485,807483,807481,807480,807477,807475,807474,807471,807469,807466,807462,807461,807460,807459,807458,807457,807455,807454,807453,807452,807449,807447,807446,807445,807443,807442,807440,807439,807427,807425,807424,807423,807421,807420,807417,807416,807415,807414,807412,807410,807407,807403,807397,807396,807395,807393,807392,807388,807387,807385,807382,807381,807379,807377,807376,807373,807370,807368,807366,807364,807363,807360,807359,807357,807356,807355,807354,807353,807352,807350,807349,807348,807347,807346,807344,807342,807338,807336,807328,807326,807308,807306,807305,807304,807302,807300,807299,807296,807293,807289,807288,807282,807268,807256,807255,807253,807239,807234,807230,807218,807216,807215,807214,807213,807209,807205,807204,807203,807201,807200,807199,807197,807196,807193,807192,807190,807188,807183,807181,807180,807178,807177,807176,807175,807174,807173,807171,807170,807168,807167,807166,807164,807163,807161,807159,807153,807151,807149,807146,807145,807144,807143,807142,807141,807140,807139,807138,807136,807133,807132,807131,807130,807129,807123,807121,807120,807119,807117,807115,807112,807107,807104,807103,807100,807099,807098,807097,807095,807094,807092,807091,807089,807087,807085,807083,807079,807077,807072,807066,807064,807063,807058,807057,807048,807044,807038,807036,807034,807033,807032,807031,807029,807022,807017,807015,807011,807010,807004,807001,806998,806993,806985,806984,806971,806965,806964,806961,806960,806956,806951,806950,806940,806933,806923,806912,806907,806882,806881,806879,806878,806871,806865,806860,806853,806849,806847,806846,806843,806832,806830,806826,806823,806820,806809,806806,806803,806794,806791,806779,806778,806773,806772,806770,806769,806766,806764,806759,806755,806754,806753,806747,806746,806745,806741,806738,806735,806732,806729,806728,806725,806718,806717,806716,806712,806703,806697,806680,806678,806675,806667,806657,806656,806650,806639,806631,806628,806623,806620,806614,806609,806608,806606,806602,806600,806597,806596,806594,806589,806581,806577,806571,806548,806533,806524,806522,806504,806488,806486,806472,806469,806465,806460,806454,806453,806444,806436,806420,806414,806411,806410,806409,806406,806400,806396,806395,806393,806386,806385,806380,806373,806370,806352,806351,806345,806340,806335,806318,806317,806316,806313,806310,806306,806304,806303,806302,806300,806292,806291,806284,806272,806259,806258,806233,806228,806224,806219,806203,806200,806197,806196,806193,806179,806177,806168,806159,806150,806149,806144,806124,806123,806118,806115,806100,806083,806081,806049,806030,806029,806006,805973,805960,805905,805862,805842,805829,805818,805797,805777,805738,805728,805713,805699,805687,805683,805671,805668,805655,805625,805568,805535,805495,805494,805480,805461,805445,805425,805424,805403,805396,805382,805381,805370,805322,805270,805267,805246,805198,805183,805146,805142,805116,805097,805094,805076,805074,805070,805069,805063,805042,805026,805023,804976,804961,804953,804928,804927,804880,804831,804789,804780,804778,804761,804751,804750,804711,804703,804700,804698,804690,804659,804658,804650,804647,804589,804577,804424,804410,804391,804374,804334,804320,804293,804267,804263,804182,804146,804090,804087,804072,804057,804027,804002,803987,803972,803959,803951,803922,803826,803779,803692,803662,803648,803560,803544,803501,803456,803349,803346,803330,803326,803320,803258,803240,803224,803212,803201,802947,802872,802828,802414,802221,802132,802106,802100,802074,802017,802014,801469,801413,801167,801154,800745,800727,800564,800410,800310,798302,798300,797092,796852,795721,792900,792707)
AND `service_orders`.`use_auto_call` = 1
GROUP BY service_orders.id
ORDER BY auto_calls_today, min(phone_numbers.id), service_orders.id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment