Results 1 to 4 of 4
  1. #1
    Join Date
    May 2016
    Posts
    2

    Unanswered: LEFT joins inMYSQL

    Hi,
    I am getting two completely different performance metrics for my query in MYSQL, even though it is against same table and is expected to give slmost similar result set
    If write it this way, I believe which is the right way as I am restricting the population in sub query, my query runs for ever.

    SELECT d.sendermobile,dd.internalid FROM `woohoo_all_data` d
    LEFT JOIN (SELECT DISTINCT mobile, internalid FROM `customer_internal_id` WHERE mobile <>'' ) dd ON d.sendermobile=dd.mobile

    If I put the where clause outside, it runs with in two minutes..


    SELECT d.sendermobile,dd.internalid FROM `woohoo_all_data` d
    LEFT JOIN (SELECT DISTINCT mobile, internalid FROM `customer_internal_id` ) dd ON d.sendermobile=dd.mobile
    WHERE mobile <>''

    Can anyone tell me how different are these two queries internally to MYSQL and why is the first one runs longer.

    Thanks,
    Roopesh

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    They are different queries
    You dont say what you want, but I suspect version 2 is probably the one you want.
    It deoends on your data, but
    Query 1 returns all rows from woohoo and those rows from internal where the internal mobile is empty
    Query 2 returns all rows from woohoo whose mobike is not empty and all rows from internal whose mobile matches

    Query 2 may well be able to take advantage of indexes but itsprobably binning alot more data BEFORE it tries to apply the join, whereas query 1 is going to return more data
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2016
    Posts
    2
    Quote Originally Posted by healdem View Post
    They are different queries
    You dont say what you want, but I suspect version 2 is probably the one you want.
    It deoends on your data, but
    Query 1 returns all rows from woohoo and those rows from internal where the internal mobile is empty
    Query 2 returns all rows from woohoo whose mobike is not empty and all rows from internal whose mobile matches

    Query 2 may well be able to take advantage of indexes but itsprobably binning alot more data BEFORE it tries to apply the join, whereas query 1 is going to return more data
    Hi,
    Result of query 1 is what I am interested in. A true left join, which returns me all records from first table and only matching values from the second table.
    My problem is that query 1 runs for hours for my tables with each of them having 300,000 records. I do the same in MSSQL, it completes with in a couple of minutes. I am trying to understand why is it behaving so differently in MYSQL for the same data population. Are there any specific considerations I have to make when using a left join in MYSQL, which is different from MSSQL??

    Thanks,
    Roopesh

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    A lot of people have difficulties with the concept of OUTER JOINS. I always point folks to Robert Catterall's blog post from a few years back. He goes into a good bit of detail and points you to a couple of White Papers that were written by Terry Purcell. While both of these gentleman work with DB2(primarily z/os), the information supplied applies to SQL and all database platforms.

    http://catterallconsulting.blogspot....tes-right.html
    Dave

Tags for this Thread

Posting Permissions

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