SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inner Join/Left Join Question

    I have a search query that has been lagging hard ever since I added a keywords table. The table is filled with several pages of text for each company and I thought it was this perhaps that was causing the lag.

    Looking at my SQL statement, I noticed I am using an INNER JOIN on all the tables except for the keywords, and wanted to ask.

    Here is what I currently have that is returning 191 results and lagging hard
    Code SQL:
    SELECT c.id, c.company, c.description, c.TIMESTAMP, c.STATUS, l.market
    FROM company AS c
    INNER JOIN market AS m ON m.id = c.id
    INNER JOIN list AS l ON l.market_id = m.market_id
    LEFT JOIN keywords AS k ON k.id = c.id 
    WHERE ...

    Here is the same query but with INNER JOIN, returning 134 results and very quick
    Code SQL:
    SELECT c.id, c.company, c.description, c.TIMESTAMP, c.STATUS, l.market
    FROM company AS c
    INNER JOIN market AS m ON m.id = c.id
    INNER JOIN list AS l ON l.market_id = m.market_id
    INNER JOIN keywords AS k ON k.id = c.id 
    WHERE ...

    Should I be using the LEFT JOIN because it is returning more results even though it is slower (like 15 seconds)? Why the difference in results?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ripcurlksm View Post
    Why the difference in results?
    because a LEFT JOIN and an INNER JOIN are ~not~ the same thing, that's why

    i'm disappointed that after all the queries i've helped you with, you had to ask this



    my advice is to drop the keywords table from your query altogether

    reason: you're not using it

    further, it's unusual to require that the keyword's id must match the company's id (this seems totally wrong)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But I am using the keywords table, it contains a lot of info that needs to be searched...?? Its not a keywords table per say, its a body of text that is associated with each company that contains words associated with that company. I have hundreds of companies that submitted two-page summaries about their company, I take all the text from these and insert it into a single row in the keywords database.

    company
    -------------------
    1 Pepsi Co

    market
    -------------------
    1 | 1
    1 | 2
    1 | 3

    list
    -------------------
    1 soda
    2 chips
    3 water

    keywords
    -------------------
    1 Pepsi was founded in 1900 by John Doe. Lots of products like Fritos, Pepsi, Diet Pepsi, Pepsi Crystal, Dasani Water and many others were brought to market by Pepsi.
    So with the info above, I am joining all of these elements together into one search so if someone searches on "chips", "John Doe" or "Dasani", it will come up in a search.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    so you ahve conditions on the keywords in the WHERE clause that aren't visible?

    that would definitely mean that you should be using INNER JOIN

    what about the fact that yo uwant the keyword id to be equal to the company id? and the company id to be equal to the market id?

    dat don't make sense...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First, thanks for your time Rudy -- here is how I visualize you, but with more hair

    so you have conditions on the keywords in the WHERE clause that aren't visible?

    that would definitely mean that you should be using INNER JOIN
    Yes, here is my full SQL query
    Code SQL:
    SELECT 
    	c.id , 
    	c.name , 
    	c.description , 
    	c.TIMESTAMP , 
    	c.STATUS , 
    	l.market 
    FROM company AS c 
    INNER JOIN market AS m 
     ON m.id = c.id 
    INNER JOIN market_list AS l 
     ON l.market_id = m.market_id 
    LEFT JOIN keywords AS k 
     ON k.id = c.id 
    WHERE (c.name LIKE '%$search_word%' OR c.description LIKE '%$search_word%' OR l.market LIKE '%$search_word%' OR k.keyword LIKE '%$search_word%') AND c.STATUS='published' GROUP BY c.name

    what about the fact that you want the keyword id to be equal to the company id?
    Yes, there is only one keyword row (LONGTEXT)per company. This contains several pages of paragraphs. I match the company id with the keyword id.

    and the company id to be equal to the market id?
    No -- a company can be in more than one category, in this example: soda, water and chips. I use the "market" table (aka category) to assign categories to companies by using that companies id and the market id from the "list". So the values you see in "market" is the id for Pepsi (1) and the corresponding id's for soda, water and chips (1,2,3)

    -----------

    To go back to the original question, an INNER JOIN will pull all of the rows, a LEFT JOIN will pull only the rows that match up with the table to the left of it. I want to make sure I do this correctly and so I'm asking this question because I am getting LESS results with INNER, and more results with LEFT.. which seems odd b/c LEFT would be more limiting.

    Also you will see in my SQL a GROUP BY, this is because companies are in multiple categories/markets and when I JOIN the 'markets' and 'list' to the query, I naturally get repeat results.

    Are some of your questions regarding the matching of id's because you're an advocate of foreign ids?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ripcurlksm View Post
    Yes, there is only one keyword row (LONGTEXT)per company. This contains several pages of paragraphs. I match the company id with the keyword id.
    then your use of the name "keyword" for this table is misleading


    Quote Originally Posted by ripcurlksm View Post
    To go back to the original question, an INNER JOIN will pull all of the rows, a LEFT JOIN will pull only the rows that match up with the table to the left of it.
    actually, it is the other way around

    an INNER JOIN returns only those rows where the join condition is satisfied

    a LEFT OUTER JOIN returns all rows from the left table, whether or not the join condition is satisfied

    Quote Originally Posted by ripcurlksm View Post
    Also you will see in my SQL a GROUP BY, this is because companies are in multiple categories/markets and when I JOIN the 'markets' and 'list' to the query, I naturally get repeat results.
    adding GROUP BY to a query that is returning "repeat results" is usually the wrong (inefficient) strategy -- better would be to avoid retrieving the "repeat results"

    in this particular case, you're retrieving multiple markets for each company but showing only one of them -- why would you do that, especially if the one that is shown is indeterminate?

    Quote Originally Posted by ripcurlksm View Post
    Are some of your questions regarding the matching of id's because you're an advocate of foreign ids?
    no, it's because of the weird table and column names

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •