MySQL query optimisation

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;

Part of the problem MAY be your postcode criteria… First, you have duplicate numbers in the criteria, secondly you should try to use something else other than IN() if possible. Your join syntax is incorrect: you cant start a LEFT join and then decide to switch an INNER, just doesn’t jive.

Start building the query table by table, using LEFT JOIN. Because your criteria is on the jobs table, I’d start there, and then bring in the extra stuff, just because you want data in a certain order in your select statement, does not mean you should be using that join order…


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
FROM
jobs
LEFT JOIN companies on jobs.company_id = companies.id
where
jobs.status = 'on'

I’m not 100% sure what your trying to do with the rest of it, but I feel like you may need a new column into your schema.

I would first remove all JOINS to select statements and join directly to tables. This can have a dramatic improvement in performance as the result of the SELECT statement is stored in a temporary table without indexes. If the number of rows returned is small then these entries will remain in memory but if they are large they will be stored in MyISAM temporary tables.

From what I can see you are simply performing GROUP_CONCAT which is used for presentation purposes of the data in the subquery, this could easily be performed in the main query as well.

no, this will blow up the results

there are several one-to-many relationships in play here

the purpose of the subqueries in the FROM clause is to produce results consisting of many rows collapsed into one, so that when these results are joined, there remains one row per main entity

if you join to the tables instead of those subqueries, you will get many times many times many times many rows per main entity – i.e. cross join effects

Let us know what you have after you have cleaned up your code a bit, corrected your joins and removed duplicate IN() criteria. There are a few other things that need cleaned up after that, such as your GROUP BY CLAUSE, but I want to see if we can get rid of that all together as any aggregate function will increase run time.