SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot Makavely's Avatar
    Join Date
    Jul 2004
    Location
    Romania
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy need help with a query

    Hello Guys!

    I'm having problem with a query for over 2 years. I really don't know what to do. Mysql drives me CRAZY. So, i have 4 tables, jobs, jobs_cities, jobs_categs and jobs_types. Last 3 tables contain the id_job field. The problem is that if i make a search and join all tables the query is taking more then 30 seconds.....and all rows from all tables does not exceed 15.000 rows.

    here is the query:


    SELECT DISTINCT(j.id) as id_unic,j.* , jc.jobcategories , jt.jobtypes, jci.jobcities

    FROM jobs j

    left outer join
    (select id_job , group_concat(jci.id_city) as jobcities from jobs_cities jci group by id_job ) as jci on jci.id_job = j.id

    left outer join ( select id_job , group_concat(jc.id_categ) as jobcategories from jobs_categs jc group by id_job ) as jc on jc.id_job = j.id

    left outer join ( select id_job , group_concat(jt.job_type) as jobtypes from jobs_type jt group by id_job ) as jt on jt.id_job = j.id

    WHERE j.status = 'OK' AND j.deleted = 'NO' AND j.suspend = 'NOK' AND j.date_end > NOW() AND (j.title LIKE '%a%' OR j.description LIKE '%a%') AND jobcities = '1' AND jobcategories = '1' AND jobtypes = '1' ORDER BY j.date_begin DESC LIMIT 0, 10

    PLEASE HELP GUYS!!
    THANKS IN ADVANCE!!!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    over two years??!! whoa!!!

    try this --
    Code:
    SELECT j.* 
         , jc.jobcategories 
         , jt.jobtypes
         , jci.jobcities 
      FROM jobs j 
    LEFT OUTER 
      JOIN ( SELECT id_job 
                  , GROUP_CONCAT(id_city) AS jobcities 
               FROM jobs_cities  
             GROUP BY id_job ) AS jci 
        ON jci.id_job = j.id 
    LEFT OUTER 
      JOIN ( SELECT id_job 
                  , GROUP_CONCAT(id_categ) AS jobcategories 
               FROM jobs_categs  
             GROUP BY id_job ) AS jc 
        ON jc.id_job = j.id 
    LEFT OUTER 
      JOIN ( SELECT id_job 
                  , GROUP_CONCAT(job_type) AS jobtypes 
               FROM jobs_type  
             GROUP BY id_job ) AS jt 
        ON jt.id_job = j.id 
     WHERE j.status = 'OK' 
       AND j.deleted = 'NO' 
       AND j.suspend = 'NOK' 
       AND j.date_end > NOW() 
       AND ( j.title LIKE '%a%' 
          OR j.description LIKE '%a%'
           ) 
    -- AND jobcities = '1'      -- notice these
    -- AND jobcategories = '1'  -- are all
    -- AND jobtypes = '1'       -- commented out
    ORDER 
        BY j.date_begin DESC LIMIT 0, 10
    the problem is, your query would only work for jobs which:
    - are related only to city 1
    and
    - are related only to category 1
    and
    - are related only to type 1

    i bet there are very few jobs that meet those criteria

    what were you thinking of trying to do with those conditions that i commented out?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Makavely's Avatar
    Join Date
    Jul 2004
    Location
    Romania
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post

    the problem is, your query would only work for jobs which:
    - are related only to city 1
    and
    - are related only to category 1
    and
    - are related only to type 1

    i bet there are very few jobs that meet those criteria

    what were you thinking of trying to do with those conditions that i commented out?
    I need those filters to work. I know that if i comment those lines the query will work fine. The problem occur when all filters are active
    Any ideas for that?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you need those filters to work in which way?

    obviously, by requiring that all three be equal to '1', you're not going to get many jobs back
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oh, wait, i think i know what you are trying to do...

    could you give me a typical query for parameters involving categories, cities, and types? i'm interested in understanding if there are any AND/OR scenarios that have to be supported

    for instance, searching for a job involving HTML and CSS is going to return fewer results than a job involving HTML or CSS

    if all you want is ANDs, across categories, cities, and types, then there's probably an easier way to do this query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot Makavely's Avatar
    Join Date
    Jul 2004
    Location
    Romania
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm not interested in obtaining more job results, just trying to do the ANDs.

    thanks

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    SELECT j.* 
      FROM jobs j 
     WHERE j.status = 'OK' 
       AND j.deleted = 'NO' 
       AND j.suspend = 'NOK' 
       AND j.date_end > NOW() 
       AND ( j.title LIKE '%a%' 
          OR j.description LIKE '%a%'
           ) 
    ----------
       AND j.id IN
           ( SELECT id_job 
               FROM jobs_cities  
              WHERE id_city = $city )
    ----------
       AND j.id IN
           ( SELECT id_job 
               FROM jobcategories  
              WHERE id_categ = $categ )
    ----------
       AND j.id IN
           ( SELECT id_job 
               FROM jobtypes  
              WHERE job_type = $type )
    ----------
    ORDER 
        BY j.date_begin DESC LIMIT 0, 10
    the $city, $categ, and $type variables need to be tested for in the form which is submitted, and that part of the WHERE clause omitted if the associated variable is not specified
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot Makavely's Avatar
    Join Date
    Jul 2004
    Location
    Romania
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    PERFECT (Query took 0.0139 sec). The IN function is a miracle when it comes to search multiple tables. Thank you very much for helping me out.


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
  •