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?
Best Answer
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.
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
WHEREclause.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.