Slow query joining on subqueries with max

Question

I'm creating a report based on user input (i.e. a search tool) that grabs data from eight tables and will output them to the screen.

Here are the create table statements. I'm very aware of how badly some of these tables were made, but I don't have the option of changing them any time soon.

CREATE TABLE `exp_z_srs_tickets` (
  `id` bigint(25) unsigned NOT NULL AUTO_INCREMENT COMMENT 'e.g. 1',
  `srs_id` bigint(20) NOT NULL COMMENT 'e.g. 250; i.e. Ticket ''title''',
  `status` int(5) NOT NULL COMMENT 'e.g. draft, approved, rejected, saved',
  `position` int(5) NOT NULL,
  `wfrl1` int(5) NOT NULL COMMENT 'e.g. consult, workflow, report',
  `wfrl2` int(5) NOT NULL COMMENT 'e.g. revision',
  `wfrl3` int(5) NOT NULL,
  `home_dept_cd` varchar(50) CHARACTER SET latin1 NOT NULL COMMENT 'e.g. 071234',
  `target_eid` varchar(9) CHARACTER SET latin1 DEFAULT NULL,
  `updated_by_eid` varchar(9) CHARACTER SET latin1 NOT NULL DEFAULT '0',
  `date` datetime NOT NULL COMMENT 'e.g. 2013-02-22 14:39',
  `date_unix` varchar(10) CHARACTER SET latin1 NOT NULL,
  `transx` varchar(50) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`),
  KEY `srs_id` (`srs_id`),
  KEY `status` (`status`),
  KEY `position` (`position`),
  KEY `wfrl3` (`wfrl3`),
  KEY `wfrl2` (`wfrl2`)
) ENGINE=InnoDB AUTO_INCREMENT=269059 DEFAULT CHARSET=utf8

CREATE TABLE `exp_z_srs_ticket_meta` (
  `id` bigint(30) unsigned NOT NULL AUTO_INCREMENT,
  `srs_id` bigint(25) unsigned NOT NULL,
  `version_id` bigint(25) unsigned NOT NULL,
  `meta_key` varchar(85) CHARACTER SET latin1 NOT NULL,
  `meta_value` longtext CHARACTER SET latin1 NOT NULL,
  `updated_unix` varchar(10) CHARACTER SET latin1 NOT NULL,
  `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `meta_key` (`meta_key`),
  KEY `srs_id` (`srs_id`)
) ENGINE=InnoDB AUTO_INCREMENT=739901 DEFAULT CHARSET=utf8

CREATE TABLE `exp_z_dept_assign` (
  `LER` varchar(9) DEFAULT NULL,
  `LER_2` varchar(9) DEFAULT NULL,
  `SC` varchar(9) DEFAULT NULL,
  `CSR` varchar(9) DEFAULT NULL,
  `CSR_2` varchar(9) DEFAULT NULL,
  `BFTS` varchar(9) DEFAULT NULL,
  `DMS` varchar(9) DEFAULT NULL,
  `FSAP` varchar(9) DEFAULT NULL,
  `SC_2` varchar(9) DEFAULT NULL,
  `ISS` varchar(9) DEFAULT NULL,
  `MSO` varchar(9) DEFAULT NULL,
  `MSO_2` varchar(9) DEFAULT NULL,
  `DEPT_HR` varchar(9) DEFAULT NULL,
  `DEPT_HR_2` varchar(9) DEFAULT NULL,
  `DEPT_HR_3` varchar(9) DEFAULT NULL,
  `DEPT_GME_F` varchar(9) DEFAULT NULL,
  `DEPT_GME_R` varchar(9) DEFAULT NULL,
  `DEPT_FIN` varchar(50) DEFAULT NULL,
  `DEPT_PAYROLL` varchar(9) DEFAULT NULL,
  `AA_UNIT` varchar(4) DEFAULT NULL,
  `LEVEL_2_DEPT_CD` varchar(8) DEFAULT NULL,
  `LEVEL_2_DEPT_TITLE` varchar(60) DEFAULT NULL,
  `LEVEL_3_DEPT_CD` varchar(8) DEFAULT NULL,
  `LEVEL_3_DEPT_TITLE` varchar(60) DEFAULT NULL,
  `LEVEL_4_DEPT_CD` varchar(8) DEFAULT NULL,
  `LEVEL_4_DEPT_TITLE` varchar(60) DEFAULT NULL,
  `LEVEL_5_DEPT_CD` varchar(8) DEFAULT NULL,
  `LEVEL_5_DEPT_TITLE` varchar(60) DEFAULT NULL,
  `LEVEL_6_DEPT_CD` varchar(8) DEFAULT NULL,
  `LEVEL_6_DEPT_TITLE` varchar(60) DEFAULT NULL,
  `LEVEL_7_DEPT_CD` varchar(8) DEFAULT NULL,
  `LEVEL_7_DEPT_TITLE` varchar(60) DEFAULT NULL,
  `DEPT_CD` varchar(8) DEFAULT NULL,
  `DEPT_TITLE` varchar(60) DEFAULT NULL,
  `CountOfEMPLOYEE_ID` int(11) DEFAULT NULL,
  `DEPT_ORG_UNIT` varchar(4) DEFAULT NULL,
  `DEPT_ORG_UNIT_TITLE` varchar(60) DEFAULT NULL,
  `LAYOFF_UNIT` varchar(100) DEFAULT NULL,
  `CLUSTER` varchar(1) DEFAULT NULL,
  `DEPT_CD_LEVEL` varchar(20) DEFAULT NULL,
  `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `LEVEL_2_DEPT_CD` (`LEVEL_2_DEPT_CD`,`DEPT_CD`,`DEPT_TITLE`) USING BTREE,
  UNIQUE KEY `LER` (`LER`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `SC` (`SC`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `CSR` (`CSR`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `DMS` (`DMS`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `SC_2` (`SC_2`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `DEPT_CD` (`DEPT_CD`) USING BTREE,
  UNIQUE KEY `MSO` (`MSO`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `DEPT_HR` (`DEPT_HR`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `MSO_2` (`MSO_2`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `DEPT_HR_2` (`DEPT_HR_2`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `DEPT_HR_3` (`DEPT_HR_3`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `DEPT_GME_F` (`DEPT_GME_F`,`DEPT_CD`) USING BTREE,
  UNIQUE KEY `LER_2` (`LER_2`,`DEPT_CD`) USING BTREE,
  KEY `ISS` (`ISS`) USING BTREE,
  KEY `LEVEL_3_DEPT_CD` (`LEVEL_3_DEPT_CD`) USING BTREE,
  KEY `DEPT_CD_key` (`DEPT_CD`) USING BTREE,
  KEY `DEPT_TITLE` (`DEPT_TITLE`) USING BTREE,
  KEY `CLUSTER` (`CLUSTER`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

CREATE TABLE `exp_z_srs_ticket_role_meta` (
  `id` bigint(25) NOT NULL AUTO_INCREMENT,
  `srs_id` bigint(25) unsigned NOT NULL,
  `version_id` bigint(5) unsigned NOT NULL DEFAULT '1',
  `role` varchar(255) CHARACTER SET latin1 NOT NULL,
  `eid` varchar(10) CHARACTER SET latin1 NOT NULL,
  `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `srs_id` (`srs_id`),
  KEY `role` (`role`),
  KEY `eid` (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=650763 DEFAULT CHARSET=utf8

CREATE TABLE `exp_z_srs_tix_status` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

CREATE TABLE `exp_z_srs_tix_position` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `group` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

CREATE TABLE `exp_z_srs_users` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `eid` varchar(9) CHARACTER SET latin1 NOT NULL,
  `name` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `email` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `home_dept_code` varchar(6) CHARACTER SET latin1 DEFAULT NULL,
  `status` varchar(1) CHARACTER SET latin1 DEFAULT NULL,
  `last_login` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `supervisor_eid` varchar(9) CHARACTER SET latin1 DEFAULT NULL,
  `last_activity` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `home_dept_code` (`home_dept_code`),
  KEY `supervisor_eid` (`supervisor_eid`),
  KEY `eid` (`eid`)
) ENGINE=InnoDB AUTO_INCREMENT=56309 DEFAULT CHARSET=utf8

CREATE TABLE `exp_z_srs_transaction_types` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `key` varchar(25) DEFAULT NULL,
  `value` longtext,
  `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `child_of` bigint(20) DEFAULT NULL,
  `menu_index` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=latin1

Here is the query. Note, it contains a MASSIVE in statement. This is generated based on user input and will be changed in the future. It slows things down a bit, but not as much as some of the joins are.

 select t.srs_id, t.status, t.position, t.wfrl2, t.wfrl3, t.home_dept_cd, t.updated_by_eid, t.date, t.date_unix,
 ts.name as status_name,
 tp.name as position_name,
 da.dept_title as dept_name, da.cluster as service_center,
 tm1.meta_value as form_type,
 tm2.meta_value as target_employee_name,
 tm3.meta_value as target_eid,
 trm1.eid as initiator_eid,
 trm2.eid as approver_eid,
 u1.name as target_name,
 u2.name as initiator_name,
 u3.name as approver_name,
 tt.value as transaction_type

 from exp_z_srs_tickets t

 join (
 select max( id ) as id from exp_z_srs_tickets group by srs_id order by srs_id desc
 ) tj on tj.id = t.id
 join exp_z_srs_tix_status ts on ts.id = t.status
 join exp_z_srs_tix_position tp on tp.id = t.position 
 join exp_z_dept_assign da on da.dept_cd = t.home_dept_cd
 left join ( 
      select srs_id, meta_key, meta_value, max( version_id ) as version_id
      from exp_z_srs_ticket_meta
      where meta_key = 'rqst_category'
      group by srs_id
  ) tm1 on tm1.srs_id = t.srs_id 
 left join (
      select srs_id, meta_key, meta_value, max( version_id ) as version_id
      from exp_z_srs_ticket_meta
      where meta_key = 'target_employee_name'
      group by srs_id
  ) tm2 on tm2.srs_id = t.srs_id 
 left join (
      select srs_id, meta_key, meta_value, max( version_id ) as version_id
      from exp_z_srs_ticket_meta
      where meta_key = 'target_eid'
      group by srs_id
 ) tm3 on tm3.srs_id = t.srs_id
 join exp_z_srs_ticket_role_meta trm1 on trm1.srs_id = t.srs_id and trm1.role = 'Initiator' 
 join exp_z_srs_ticket_role_meta trm2 on trm2.srs_id = t.srs_id and trm2.role = 'Approver' 
 left join exp_z_srs_users u1 on u1.eid = tm3.meta_value 
 left join exp_z_srs_users u2 on u2.eid = trm1.eid 
 left join exp_z_srs_users u3 on u3.eid = trm2.eid 
 left join exp_z_srs_transaction_types tt on tt.id = t.wfrl3

 where 1  

 and t.home_dept_cd in (007940,008431,018661,023548,031673,101785,156093,407893,456777,650637,776725,779919,177613,257448,261415,268150,270593,272662,274198,276436,276908,278700,285676,327293,331698,336586,359542,359748,364493,373615,379469,382482,397154,400961,412351,425016,427443,447190,447546,448322,450461,450611,451004,452619,455260,455643,457115,468942,470421,525220,577058,603127,610930,642086,659497,706472,721133,727823,861464,899228,954186,228756,238629,241651,247584,252595,259295,528414,557392,877509,027973,549963,551441,552501,529271,529538,532837,545413,546635,548768,118811,109242,114646,117661,123921,127268,148101,155101,298323,331064,380180,460922,575997,899674,948336,981165,038989,070755,188790,231626,263468,288067,312717,355626,538706,538902,604723,612466,770847,773274,782264,850485,855006,892225,059215,178113,211176,442140,040823,127507,565221,752563,809664,106780,627673,097157,104354,156761,316697,941039,086739,133741,300846,587957,646395,816763,321716,390106,478263,510432,527497,336363,424963,479583,526835,541961,264216,276230,444380,538742,550665,742833,797427,053015,169267,276766,289182,301872,387405,618479,666005,696367,719949,938212,980460,035232,070461,161176,192106,211425,279255,289985,645298,649007,650183,652421,801145,890021,940334,130272,266884,279344,477638,562242,708201,836901,961463,071567,105291,142660,217492,312012,456991,513206,540365,550898,818217,979659,279399,088292,256859,320548,374696,709816,712848,726539,754213,767183,815434,838945,350863,355190,356652,467408,778698,023021,065850,083590,115949,120540,206912,273224,317801,343630,392211,420912,447207,456072,490089,588091,729064,738330,798051,847677,886599,898014,940682,953837,957110,036669,284221,301382,378754,428237,493175,623944,937730,947328,969553,996202,094285,358990,068009,157467,179274,643842,714490,741512,912882,930014,155959,281402,329754,357900,461486,466454,525836,582774,625577,638803,817824,848916,862659,888739,951474,987953,077543,185052,233376,408598,678190,701636,766095,920490,410905,567513,059616,747749,437780,048460,104764,573007,653830,812311,814328,917404,967252,026581,027624,041350,065075,119197,153176,176641,345843,457419,461850,481543,506107,530054,552798,600674,606240,617737,629270,701583,747801,799979,808166,830266,951134,955568,956594,991001,033403,053113,107342,112817,120158,137202,162102,173467,180930,204415,206967,221860,231289,280485,338931,394497,414288,417203,428004,468755,476158,478655,497616,543460,580106,652500,663972,679787,687288,769555,791441,812696,813472,839864,863532,878401,910358,948531,972157,974422,070611,102310,141876,222645,331359,424213,552020,563571,785430,989078,543521,791085,916361,985642,459417,650664,874940,031511,130405,243105,452398,479869,526345,854169,988729,162184,094882,268061,455279,457801,991804,161693,804990,883556,096540,352157,849559,306369,345674,915237,037016,053845,087792,227748,660305,782200,783094,859431,865219,945188,410353,952525,397181,876779,898256,969740,981263,086267,094784,189147,239290,252112,252443,273581,325053,370583,597884,598133,709503,712170,813720,298289,552896,875805,022996,134205,142492,201695,696296,971390,199001,207305,326365,396299,721419,952231,060454,331466,753651,876840,849309,141046,202596,275133,792654,431143,069892,161309,235613,238479,493451,962042,076820,097326,102203,102686,243025,305226,315313,357134,370930,491523,653812,710957,775263,868314,990182,113576,242650,359506,371788,124234,127632,161390,498811,529342,590006,771221,823050,989489,202408,252924,392033,415116,895534,038514,056977,135053,235506,465473,745750,767628,046015,278746,369960,670884,682238,846142,100465,182509,332214,404413,535549,636039,929507,140519,603993,711322,778377,195078,310942,347164,373768,380769,975519,019615,046569,049138,066001,072851,076599,078775,079426,084991,156075,165635,173234,328238,587476,713160,756239,111523,204406,485647,524418,023673,075420,114254,353968,584497,800743,189138,329843,461618,482098,578306,857530,246139,332651,483480,499561,830088,691219,650218,700209,540481,345665,684753,240769,513224,172967,215671,217170,221147,236131,303745,351158,453887,493059,532720,552486,598268,650888,657186,694644,699827,707738,715444,849657,886410,925048,015272,096112,132555,147601,300427,371001,433338,439528,523721,527932,629341,640104,656178,660163,682041,686056,733362,784949,880381,323359,500283,003098,024627,032770,074412,115921,181403,216509,219123,266955,276043,287843,348779,352576,413305,454458,522795,538323,551799,609470,616113,664882,701057,734922,748800,851992,866726,892626,897532,935974,959662,964969,467319,656908,255832,072600,449125,591032,641452,689133,731863,054318,528469,844288,415615,503191,933244,395389,222663,621134,198761,327836,635049,769911,466944,377684,974173,384729,009644,257279,121479,057510,329905,607212,642013,195167,008789,043973,051589,063111,064655,108467,123618,160104,169141,195835,228097,256582,258401,295503,329996,418676,419648,420440,445637,453253,463215,468504,489135,502405,559265,562331,563394,573294,581267,774950,833904,860027,903482,913248,303674,650129,671179,721614,822140,867271,969633,273545,545262,550139,746330,777154,916272,941609,188736,688376,886802,322788,555759,181029,720722,748882,029356,081752,130691,415544,460539,531400,693672,819859,845125,850332,902928,968803,158689,242856,844028,126884,184856,909253,924343,093954,252933,384499,437487,499259,575853,726370,797329,998745,009136,078702,113120,118884,290143,541300,976386,989327,031110,103989,492755,646723,115994,118615,374222,176516,348573,349894,396048,543317,666853,764774,809959,859681,948407,016912,082868,177221,475417,771613,235846,043651,187032,314190,725344,787161,873031,887758,921845,996328,177258,303255,444923,988024,988239,633504,000590,034466,146247,148218,148343,204736,457507,642674,715202,782442,787713,010794,164538,207323,210140,354280,523874,114913,477095,515160,142553,741022,125616,317142,497625,631891,410978,453903,584157,784011,812366,896695,932986,102338,276579,510977,569496,569584,725530,771953,831087,843804,926412,932012,982011,171030,310112,433276,684138,687171,758567,174670,654571,866708,897694,346762,124653,161452,478021,877144,180477,216787,428291,723319,810643,055120,290768,397984,439145,772818,519941,261576,478566,649034,662410,737983,071255,321547,615463,778322,780195,946980,462788,785056,820696,566809,638082,222501,346619,436424,327202,842681,440598,996550,370681,134624,242552,287184,374936,704223,715845,808763,915558,304441,316072,114496,365526,777573,097040,151427,337433,575274,312762,584914,903240,997559,049995,054906,300999,406331,260880,407937,435933,588304,631230,698908,491293,749006,056174,069838,113095,172921,230020,257144,283810,342819,487930,489064,509514,515437,526256,541257,549179,558836,562974,564570,632649,726352,729457,744788,749818,794894,852973,908290,909477,940076,974501,979668,991993,117812,428488,572892,763560,072334,428843,741399,997087,343140,814505,062782,954113,055825,375052,178943,384319,757309,400088,743324,222306,455787,947382,013185,016734,221085,244603,330500,362315,476666,535647,657257,863131,374623,625719,187201,964273,247496,395030,107011,729322,997229,069954,140868,237087,946524,002678,471796,504350,404477,764408,990529,321618,436656,638858,798355,111122,472205,995301,074083,530802,693841,061079,396798,735431,985740,203407,215234,219882,712704,751939,882548,998326,082582,360978,687867,035599,394077,537066,668628,672855,930238,966020,135865,137505,294096,481259,716390,799899,179988,183456,240812,359267,455055,479011,696857,594164,985303,173939,300490,320520,357367,443728,541701,559657,704562,742129,813588,884662,991163,127311,592763,108546,120489,138755,275936,372750,462699,671268,900868,195354,835485,279022,665596,816013,913765,199804,242847,256396,404510,901661,156235,229489,778572,006825,859789,952124,160131,412734,274278,364126,527344,253264,938481,055424,060837,079587,088853,185864,216411,236998,613330,821285,092483,159722,307091,546975,466007,428353,557178,425695,420146,448714,415198,414787,410193,404743,581908,435620,534648,455910,469941,449526,470920,515204,449107,478940,491710,516258,518773,396164,377014,372536,245871,255663,245513,245238,235123,232420,230431,001508,003070,258606,267286,363298,358874,341320,337344,308134,292819,281803,275838,273028,271119,222814,998040,884715,874174,957101,868537,860394,857585,847267,846295,826306,815522,811198,908539,913836,995356,985802,973414,968769,962444,957833,954630,939239,924860,919643,915246,807363,792869,785047,660724,637172,632890,632827,629733,620634,605973,600781,599445,594869,590701,668833,674210,777644,768477,768191,764079,745304,743949,734076,730043,724283,700691,676502,584898,220068,003953,162308,157788,012783,013675,016173,056450,064931,070648,078784,175535,130192,496172,211880,208260,099351,009724,818342,200231,195773,183376,083475,105870,124911,129676,930489,133830,148281,149002,143142,476853,152195,098254,151025,157261,154317,096933,087596,154111,194603,428978,643236,437469,243338,763524,698294,182778,404100,584308,602351,694430,963620,984581,047327,602397,248556,208885,294595,336728,206342,854016,497643,860223,890414,554787,648623,946052,367702,058911,052221,590168,207056,311576,680784,204852,421387,628609,430313,434685,124154,935661,301541,411833,657024,176384,226419,219141,908879,877297,076090,033234,190153,923004,095881)

 group by t.srs_id 
 order by t.srs_id desc

Here's the explain for the query.

+----+-------------+-----------------------+--------+--------------------------------+-------------+---------+---------------------------------+--------+----------------------------------------------+
| id | select_type | table                 | type   | possible_keys                  | key         | key_len | ref                             | rows   | Extra                                        |
+----+-------------+-----------------------+--------+--------------------------------+-------------+---------+---------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>            | ALL    | NULL                           | NULL        | NULL    | NULL                            |  47863 | Using temporary; Using filesort              |
|  1 | PRIMARY     | t                     | eq_ref | PRIMARY,srs_id,status,position | PRIMARY     | 8       | tj.id                           |      1 | Using where                                  |
|  1 | PRIMARY     | da                    | ref    | DEPT_CD,DEPT_CD_key            | DEPT_CD_key | 11      | zucsfhr_DEVEE150.t.home_dept_cd |      1 | Using where                                  |
|  1 | PRIMARY     | ts                    | eq_ref | PRIMARY                        | PRIMARY     | 4       | zucsfhr_DEVEE150.t.status       |      1 |                                              |
|  1 | PRIMARY     | trm1                  | ref    | srs_id,role                    | srs_id      | 8       | zucsfhr_DEVEE150.t.srs_id       |      2 | Using where                                  |
|  1 | PRIMARY     | tp                    | eq_ref | PRIMARY                        | PRIMARY     | 4       | zucsfhr_DEVEE150.t.position     |      1 |                                              |
|  1 | PRIMARY     | <derived3>            | ALL    | NULL                           | NULL        | NULL    | NULL                            |  47343 |                                              |
|  1 | PRIMARY     | <derived4>            | ALL    | NULL                           | NULL        | NULL    | NULL                            |  46682 |                                              |
|  1 | PRIMARY     | <derived5>            | ALL    | NULL                           | NULL        | NULL    | NULL                            |  36743 |                                              |
|  1 | PRIMARY     | u1                    | ref    | eid                            | eid         | 11      | tm3.meta_value                  |      1 |                                              |
|  1 | PRIMARY     | u2                    | ref    | eid                            | eid         | 11      | zucsfhr_DEVEE150.trm1.eid       |      1 |                                              |
|  1 | PRIMARY     | trm2                  | ref    | srs_id,role                    | srs_id      | 8       | zucsfhr_DEVEE150.t.srs_id       |      2 | Using where                                  |
|  1 | PRIMARY     | u3                    | ref    | eid                            | eid         | 11      | zucsfhr_DEVEE150.trm2.eid       |      1 |                                              |
|  1 | PRIMARY     | tt                    | eq_ref | PRIMARY                        | PRIMARY     | 8       | zucsfhr_DEVEE150.t.wfrl3        |      1 |                                              |
|  5 | DERIVED     | exp_z_srs_ticket_meta | ref    | meta_key                       | meta_key    | 87      |                                 |  75554 | Using where; Using temporary; Using filesort |
|  4 | DERIVED     | exp_z_srs_ticket_meta | ref    | meta_key                       | meta_key    | 87      |                                 |  98574 | Using where; Using temporary; Using filesort |
|  3 | DERIVED     | exp_z_srs_ticket_meta | ref    | meta_key                       | meta_key    | 87      |                                 | 102798 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | exp_z_srs_tickets     | index  | NULL                           | srs_id      | 8       | NULL                            | 260955 | Using index                                  |
+----+-------------+-----------------------+--------+--------------------------------+-------------+---------+---------------------------------+--------+----------------------------------------------+

I'm very certain that the three tm joins ( tm1, tm2, tm3 ) are the culprit. If I remove the subquery and do a simple join on those tables, the query is much faster. As it is now, I don't know how long the query takes to run. I gave up after about five minutes of waiting.

How would you recommend rewriting this?

Answer 1

The problem with the query as it is written comes from two aspects:

• Your subqueries are generating derived tables that are not correlated to the outer query, so MySQL is rendering all possible records from each of those subqueries into three temporary tables, and then joining them to the tables in the outer query, and

• The join from the temporary tables to the outer tables isn't using any indexes, since the derived temporary tables don't have indexes.

@Mark Bannister has proposed a good rewrite of your query which should be a significant improvement, but I believe there is more that could be done.

I think the best way to write this query may be to write it from the inside out.

 select t.srs_id, t.status, t.position, t.wfrl2, t.wfrl3, 
    t.home_dept_cd, t.updated_by_eid, t.date, t.date_unix,
    ts.name as status_name,
    tp.name as position_name,
    da.dept_title as dept_name, da.cluster as service_center,
    tm.meta_value1 as form_type,
    tm.meta_value2 as target_employee_name,
    tm.meta_value3 as target_eid,
    trm.eid1 as initiator_eid,
    trm.eid2 as approver_eid,
    u1.name as target_name,
    u2.name as initiator_name,
    u3.name as approver_name,
    tt.value as transaction_type
from (
    SELECT tx.srs_id, tx.status, tx.position, tx.wfrl2, tx.wfrl3, 
    tx.home_dept_cd, tx.updated_by_eid, tx.date, tx.date_unix,
    (SELECT id FROM exp_z_srs_ticket_meta ezstm
     WHERE ezstm.srs_id = tx.srs_id ORDER BY version_id DESC LIMIT 1) as meta_latest_id
    FROM exp_z_srs_tickets tx

    where 1  

     and t.home_dept_cd in (007940,008431,...)

) t JOIN ...
LEFT JOIN ...

So now, the 't' table is not the tickets, but is a derived table itself with the records from the ticket table.

Instead of finding the id corresponding to the highest version for every possible srs_id in the exp_z_srs_ticket_meta table, we're doing individual lookups for only the rows in exp_z_srs_tickets we're interested in, based on the WHERE clause.

This implicit temporary table should be derived first by the optimizer, and the rest of your query would use joins or left joins as appropriate, to look up the values you need, using the column meta_latest_id. It seems like you could eliminate all of the other subqueries and replace them with joins, to join the single derived table to all the lookup tables you need. The derived table won't have indexes, but they are less important there, because they are more valuable to us for lookups, and the indexes on the lookup tables (if defined) should get used for the joins.

Answer 2

1. Remove the order by clause from the tj inline view.
2. Combine the three inline views querying exp_z_srs_ticket_meta into a single inline view, and remove the unnecessary selection of meta_key and version_id from it.
3. Combine the two separate joins to exp_z_srs_ticket_role_meta to a single inline view.

- so the resultant query might look something like:

 select t.srs_id, t.status, t.position, t.wfrl2, t.wfrl3, t.home_dept_cd, t.updated_by_eid, t.date, t.date_unix,
        ts.name as status_name,
        tp.name as position_name,
        da.dept_title as dept_name, da.cluster as service_center,
        tm.meta_value1 as form_type,
        tm.meta_value2 as target_employee_name,
        tm.meta_value3 as target_eid,
        trm.eid1 as initiator_eid,
        trm.eid2 as approver_eid,
        u1.name as target_name,
        u2.name as initiator_name,
        u3.name as approver_name,
        tt.value as transaction_type
 from exp_z_srs_tickets t
 join (select max( id ) as id from exp_z_srs_tickets group by srs_id) tj on tj.id = t.id
 join exp_z_srs_tix_status ts on ts.id = t.status
 join exp_z_srs_tix_position tp on tp.id = t.position 
 join exp_z_dept_assign da on da.dept_cd = t.home_dept_cd
 left join
      (select srs_id, 
              max(case meta_key when 'rqst_category' then meta_value end) meta_value1,
              max(case meta_key when 'target_employee_name' then meta_value end) meta_value2,
              max(case meta_key when 'target_eid' then meta_value end) meta_value3
       from exp_z_srs_ticket_meta
       where meta_key in ('rqst_category','target_employee_name','target_eid')
       group by srs_id) tm on tm.srs_id = t.srs_id 
 join (select srs_id,  
              max(case role when 'Initiator' then eid end) eid1,
              max(case role when 'Approver' then eid end) eid2
       from exp_z_srs_ticket_role_meta
       where role in ('Initiator','Approver')
       group by srs_id) trm on trm.srs_id = t.srs_id
 left join exp_z_srs_users u1 on u1.eid = tm.meta_value3
 left join exp_z_srs_users u2 on u2.eid = trm.eid1
 left join exp_z_srs_users u3 on u3.eid = trm.eid2
 left join exp_z_srs_transaction_types tt on tt.id = t.wfrl3
 where 1 and t.home_dept_cd in (...)
 group by t.srs_id 
 order by t.srs_id desc

I would also try to run the original query to completion (possibly with a smaller set of home_dept_cd values to be selected) to obtain a figure for query time, and try implementing each of these measures separately, together with obtaining a query time for each of them, to establish the impact upon performance of each of them.