SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Feb 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble with joining tables

    I'm having some trouble running the following query:

    SELECT DISTINCT(im.id) FROM item_master im LEFT JOIN xref xr ON im.id = xr.item_master_id WHERE im.item_number LIKE '%100%' OR xr.oem LIKE '%100%'

    For some reason, the query takes 2 minutes to execute. If I remove the bit that says "im.item_number LIKE '%100%'", or change it to compare data from the joined table (eg. "xr.item_number LIKE '%100%'"), the query is executed in 31ms.

    What is wrong here?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i bet that item_master has lots of rows in it, and xref either has few rows or not many that have matching rows in the item_master table.

    LIKE '%...%' can not utilize an index. therefore, mysql must examine every row in the table. if you have lots of rows, that will take awhile.

    why do you need to match the middle of a string like that?

  3. #3
    SitePoint Member
    Join Date
    Feb 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    item_master has almost 13000 rows, and the xref table has 16000+.

    Every row in item_master can have multiple rows in xref, since one item can have multiple "item_number"s (called oem in the xref table).

    The thing is, if I use INNER JOIN it only take 47ms. I was just wondering what the big difference between LEFT and INNER join is in this case.

    I only used '%100%' as an example. It doesn't really matter what I match it up against. As long as I match values in both tables, it takes forever (with LEFT JOIN).

    Oh, and I need to use LEFT JOIN, because not all rows in item_master has a reference in xref
    Last edited by jezzdk; Feb 14, 2007 at 19:50. Reason: forgot something

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    is xr.item_master_id indexed?

    i think that because you have the xr.oem LIKE '%100%' in the where clause with an OR, mysql has to first join all of the rows together, then filter them. that's at least 13k rows that need to be joined before the WHERE clause is applied. that's why it takes so long.

  5. #5
    SitePoint Member
    Join Date
    Feb 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there an alternative method?
    The only thing I could think of, was to first collect all the matches in the item_master table, THEN search through the xref table, find the corresponding id in the item_master, and then only add to the collection then ids that wasn't found in the first search.
    I only need the item_master.id field.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    why distinct(im.id)? how are you using this query?

    if you've modified the query for testing or to post it, please post the actual query you will be using.

  7. #7
    SitePoint Member
    Join Date
    Feb 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the exact query I'm trying to run:

    SELECT DISTINCT(im.id) FROM item_master im LEFT JOIN xref xr ON im.id = xr.item_master_id
    WHERE im.item_number LIKE '%202528%'
    OR xr.oem LIKE '%202528%'
    I use DISTINCT in case the same im.id is returned multiple times. I wouldn't know if it did, cos I can't get the damn query to run

    I need to get all the rows in the item_master where the item_number contains a given string, AND I need to get the rows where it's oem numbers (in the xref) contains the given string.

    I could divide it into two separate querys, but I'm stubborn...

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jezzdk View Post
    Is there an alternative method?
    yeppir
    Code:
    select id 
      from item_master  
     where item_number like '%202528%'   
    union
    select item_master_id 
      from xref
     where oem like '%202528%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Feb 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Damn... never thought it would be so easy
    Haven't used the UNION command before either


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
  •