Professional Web Applications Themes

Help optimize query - MySQL

Hi guys, I have a short query that is taking a while to run. I'd appreciate some help. Let me give you the breakup of the individual query results : select distinct uid from table_A : returns 160K records in 4 seconds. select distinct uid from table_B : returns 120K records in 4 seconds. Here's the query that is taking forever to run (hoping to optimize this one) select distinct uid from table_A where uid not in (select distinct uid from table_B) Any input would be appreciated? I was thinking of setting up a primary key on uid, create indexes. ...

  1. #1

    Default Help optimize query

    Hi guys,

    I have a short query that is taking a while to run. I'd appreciate some
    help.

    Let me give you the breakup of the individual query results :
    select distinct uid from table_A : returns 160K records in
    4 seconds.
    select distinct uid from table_B : returns 120K records in
    4 seconds.

    Here's the query that is taking forever to run (hoping to optimize this
    one)

    select distinct uid from table_A where uid not in
    (select distinct uid from table_B)

    Any input would be appreciated? I was thinking of setting up a primary
    key on uid, create indexes. Maybe there is a better way to write the
    query. My goal is to basically get difference in uid's between the 2
    tables.. so I am hoping to get 40K rows.

    -Anil

    anilcool@gmail.com Guest

  2. #2

    Default Re: Help optimize query

    [email]anilcool[/email] wrote:
    > Hi guys,
    >
    > I have a short query that is taking a while to run. I'd appreciate
    > some help.
    >
    > Let me give you the breakup of the individual query results :
    > select distinct uid from table_A : returns 160K records in
    > 4 seconds.
    > select distinct uid from table_B : returns 120K records in
    > 4 seconds.
    >
    > Here's the query that is taking forever to run (hoping to optimize
    > this one)
    >
    > select distinct uid from table_A where uid not in
    > (select distinct uid from table_B)
    >
    > Any input would be appreciated? I was thinking of setting up a primary
    > key on uid, create indexes. Maybe there is a better way to write the
    > query. My goal is to basically get difference in uid's between the 2
    > tables.. so I am hoping to get 40K rows.
    >
    > -Anil
    Try
    select distinct uid from table_A where exists select '1' from table_B where
    table_A.uid = table_B.uid


    Paul Lautman Guest

  3. #3

    Default Re: Help optimize query

    Hi Paul,

    Since I am trying to use the difference (NOT IN) clause, should I
    change the query that you posted to end as:

    table_A.uid <> table_B.uid

    I'm guessing that table_A.uid = table_B.uid would give me the IN clause
    equivalent.

    The other option might be to use NOT EXISTS.

    Thanks,
    -Anil

    anilcool@gmail.com Guest

  4. #4

    Default Re: Help optimize query

    [email]anilcool[/email] wrote:
    > Hi Paul,
    >
    > Since I am trying to use the difference (NOT IN) clause, should I
    > change the query that you posted to end as:
    >
    > table_A.uid <> table_B.uid
    >
    > I'm guessing that table_A.uid = table_B.uid would give me the IN
    > clause equivalent.
    >
    > The other option might be to use NOT EXISTS.
    >
    > Thanks,
    > -Anil
    I had intended to write NOT EXISTS, but I found the NOT on the floor under
    my desk ;-)


    Paul Lautman Guest

  5. #5

    Default Re: Help optimize query

    Here's the classic one (works on older MySQL versions also):

    SELECT DISTINCT table_A.uid FROM table_A LEFT JOIN table_B ON
    table_A.uid=table_B.uid WHERE table_B.uid IS NULL

    I assume you have indexes on the uid fields?

    [email]anilcool[/email] wrote:
    > Hi guys,
    >
    > I have a short query that is taking a while to run. I'd appreciate some
    > help.
    >
    > Let me give you the breakup of the individual query results :
    > select distinct uid from table_A : returns 160K records in
    > 4 seconds.
    > select distinct uid from table_B : returns 120K records in
    > 4 seconds.
    >
    > Here's the query that is taking forever to run (hoping to optimize this
    > one)
    >
    > select distinct uid from table_A where uid not in
    > (select distinct uid from table_B)
    >
    > Any input would be appreciated? I was thinking of setting up a primary
    > key on uid, create indexes. Maybe there is a better way to write the
    > query. My goal is to basically get difference in uid's between the 2
    > tables.. so I am hoping to get 40K rows.
    >
    > -Anil
    >
    Dikkie Dik Guest

Similar Threads

  1. PDF optimize
    By Ashish in forum Brainstorming Area
    Replies: 0
    Last Post: June 30th, 09:12 AM
  2. PDF won't optimize
    By Beth_R_Phillips@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 3
    Last Post: February 8th, 11:25 PM
  3. Optimize in batch?
    By Lefteris_Koumis@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: May 12th, 06:50 AM
  4. How to optimize?
    By Krillo webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 1
    Last Post: August 4th, 06:38 PM
  5. optimize for n rows
    By xixi in forum IBM DB2
    Replies: 2
    Last Post: July 3rd, 08:38 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139