SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code:
    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;

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    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...

    Code:
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rcashell View Post
    I would first remove all JOINS to select statements and join directly to tables.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •