Hi,
I have a very slow MySQL query (taking over 9 secs to return any result). I was wondering if it is at all possible to speed up the query. I’ve asked this question before, but never really got a definitive answer. Here’s the query. Both the job_id and postcode columns in the “job_locations” database are indexed.
select companies.company_name,
companies.company_url,
companies.logo_thumbnail,
jobs.job_title,
jobs.job_url,
jobs.job_salary,
jobs.job_ote,
jobs.job_perks,
jobs.job_description,
jobs.date_posted,
count(applications.id) as applications,
count(featured_jobs.id) as featured,
l.location,l.display_name,l.display_county,
i.industry,
j.job_type
from jobs
inner join companies on companies.id = jobs.company_id
left outer join applications on applications.job_id = jobs.id
left outer join featured_jobs on featured_jobs.job_id = jobs.id
inner join
(select job_id, group_concat(display_county SEPARATOR ',') as location,
if(count(job_id) > 1,'Locations throughout the UK', display_name) as display_name,
display_county, postcode
from jobs_locations where postcode in (89001,89001,89001,89017,89017,89017,89017,89020,89020,89020,89310,89310,89310,89310,89311,89311,89820,89820,89820,89003,89003,89004,89004,89005,89005,89006,89007,89007,89008,89008,89008,89821,89821,89821,89821,89821,89821,89822,89701,89701,89701,89701,89701,89701,89701,89701,89701,89701,89701,89701,89702,89703,89704,89704,89704,89705,89706,89706,89711,89711,89712,89712,89713,89713,89714,89714,89721,89402,89450,89450,89451,89451,89452,89452,89403,89823,89404,89404,89404,89404,89404,89404,89010,89801,89801,89801,89801,89802,89803,89815,89815,89815,89828,89301,89301,89301,89301,89301,89301,89314,89315,89405,89316,89406,89406,89406,89406,89406,89406,89406,89406,89406,89406,89406,89407,89496,89496,89408,89408,89409,89410,89410,89410,89410,89410,89410,89460,89411,89412,89412,89413,89413,89414,89414,89414,89013,89013,89013,89415,89415,89415,89415,89415,89415,89002,89009,89011,89012,89014,89014,89015,89015,89016,89044,89044,89052,89053,89074,89077,89418,89418,89418,89418,89418,89418,89418,89418,89018,89018,89018,89070,89070,89070,89825,89825,89825,89826,89019,89019,89019,89019,89026,89026,88901,88901,88905,88905,89054,89054,89101,89102,89103,89104,89105,89106,89107,89108,89109,89110,89111,89111,89112,89113,89114,89115,89116,89117,89118,89118,89119,89120,89121,89122,89123,89124,89124,89124,89124,89124,89124,89124,89124,89124,89124,89124,89124,89125,89126,89127,89128,89129,89130,89131,89132,89133,89134,89135,89136,89137,89138,89139,89140,89141,89142,89143,89144,89145,89146,89147,89148,89149,89150,89150,89151,89151,89152,89152,89153,89153,89154,89154,89155,89155,89156,89157,89158,89158,89159,89159,89160,89161,89161,89161,89162,89163,89163,89163,89164,89164,89164,89165,89166,89169,89170,89173,89177,89177,89178,89179,89180,89183,89185,89191,89191,89191,89191,89191,89193,89195,89195,89199,89028,89029,89021,89021,89021,89419,89419,89419,89419,89419,89419,89419,89419,89317,89420,89421,89421,89421,89318,89318,89022,89023,89023,89024,89027,89034,89422,89423,89025,89037,89037,89037,89067,89067,89067,89830,89831,89831,89424,89030,89030,89030,89031,89031,89032,89032,89032,89033,89033,89036,89036,89081,89081,89084,89084,89085,89085,89086,89086,89087,89087,89425,89425,89425,89040,89040,89040,89040,89040,89040,89832,89832,89041,89041,89041,89048,89048,89048,89060,89061,89042,89426,89426,89043,89043,89043,89043,89043,89043,89501,89502,89502,89503,89503,89504,89505,89506,89506,89506,89506,89506,89506,89506,89506,89506,89506,89506,89506,89507,89507,89508,89508,89508,89508,89509,89510,89510,89510,89510,89510,89510,89510,89511,89511,89511,89511,89511,89512,89513,89515,89519,89520,89521,89521,89521,89523,89523,89523,89533,89555,89555,89557,89557,89570,89595,89595,89599,89599,89045,89045,89045,89319,89427,89427,89039,89039,89039,89046,89046,89046,89428,89047,89429,89429,89429,89430,89430,89431,89431,89431,89431,89432,89433,89433,89434,89434,89434,89434,89434,89434,89435,89436,89436,89436,89441,89441,89441,89049,89049,89049,89834,89438,89439,89440,89440,89442,89442,89444,89444,89444,89444,89444,89833,89833,89835,89835,89835,89835,89835,89835,89835,89835,89835,89835,89883,89883,89883,89445,89445,89445,89445,89445,89445,89445,89445,89445,89446,89447,89447,89447,89447,89447,89448,89448,89448,89448,89448,89448,89448,89448,89448,89448,89448,89448,89448,89449,89449,89449,89824,89824,89824) group by job_id) as l on l.job_id = jobs.id
inner join
(select job_id, group_concat(industry_id SEPARATOR ',') as industry from jobs_industries group by job_id) as i on i.job_id = jobs.id
inner join
(select job_id, group_concat(job_type_id SEPARATOR ',') as job_type from jobs_job_types group by job_id) as j on j.job_id = jobs.id
where jobs.status='on' group by jobs.id
order by featured desc, date_posted desc;