Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: Left outer join takes too much time

    Hi
    There are two tables
    Table 1 : final_account_bbs_tm
    Table 2: apgvb_csp_cus_mapping
    same column in both table global_account_num

    I want records for table final_account_bbs_tm which does not exist in table
    global_account_num for column global_account_num

    Following is the query But it take so long long hours and finally too much load on mysql

    Code:
    select fa.global_account_num , asm.global_account_num 
      from final_account_bbs_tm fa LEFT OUTER JOIN
           apgvb_csp_cus_mapping asm ON fa.global_account_num=asm.global_account_num 
           where asm.global_account_num  is  null;
    Its EXPLAIN output

    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: fa
    type: index
    possible_keys: NULL
    key: global_account_num
    key_len: 303
    ref: NULL
    rows: 1272300
    Extra: Using index
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: asm
    type: index
    possible_keys: NULL
    key: global_account_num_id1
    key_len: 53
    ref: NULL
    rows: 1114569
    Extra: Using where; Using index

    Is there any other way to get same result in efficient way
    Last edited by ankur02018; 11-25-10 at 08:39. Reason: change quote to code

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    a couple of items to point out in this query. First you are getting all 1272300 rows form you final account table. Why you would want that I can't even think of a reasonable answer, no one will ever read all of them. Next, you are selecting the same value twice, what good does that do for you? Do you really think any of two column pairs would not be identical when you joined on them? If you want to write a query, I think you should be specific and get just what you need. There are plenty of standards that folks have posted about for years, limit your select statement to just what you need, extra bytes cause for longer times, so if you know the value of a particular field, then do not select it. For instance your query has status = 'A', then you know the status is A and you do not have it in your select clause. You have a.col1 = b.col1, then you do not need both col1's in your select list, just one of them. The WHERE clause should have meaningful inputs to the query that will help limit what rows are returned to you, if not it will run longer.
    Dave Nance

  3. #3
    Join Date
    Jun 2007
    Posts
    197
    Quote Originally Posted by dav1mo View Post
    a couple of items to point out in this query. First you are getting all 1272300 rows form you final account table. Why you would want that I can't even think of a reasonable answer, no one will ever read all of them. Next, you are selecting the same value twice, what good does that do for you? Do you really think any of two column pairs would not be identical when you joined on them? If you want to write a query, I think you should be specific and get just what you need. There are plenty of standards that folks have posted about for years, limit your select statement to just what you need, extra bytes cause for longer times, so if you know the value of a particular field, then do not select it. For instance your query has status = 'A', then you know the status is A and you do not have it in your select clause. You have a.col1 = b.col1, then you do not need both col1's in your select list, just one of them. The WHERE clause should have meaningful inputs to the query that will help limit what rows are returned to you, if not it will run longer.
    Dave Nance
    Thanks for your suggestion

    My Requirement is
    records that exist in fa.global_account_num but not in asm.global_account_num

    output suppossed to be as follow
    Code:
    fa.global_account_num  asm.global_account_num  
    xxxxxxx                        NULL
    yyyyyy                         NULL
    qqqqqq                         NULL
    aaaaaa                         NULL
    Yes we can avoid column asm.global_account_num but it will not make much
    effect on performance

    Is there any other way to get such results in much faster way
    Last edited by ankur02018; 11-25-10 at 08:38.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    so why not write the SQL in that way?
    Code:
    select a.col 
       from table a 
    where not exists (select 1 from table b
                             where a.col = b.col)
    Dave

Posting Permissions

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