Professional Web Applications Themes

Noob question: why the huge performance difference between these two queries? - MySQL

case1: select a.field1 from foo a, (select field2 from bar where *condition*) b where a.field2 = b.field2 vs. case2: select field1 from foo a where a.field2 = any (select field2 from bar where *condition*) I'm simplifying a bit so it is possible that the actual specifics of *condition* are making a difference, but as far as my limited knowledge goes, I imagine not. case2 btw takes more than 10 times longer than case1. And aside from the specific answer to this question, could anyone recommend any online tutorials for query tuning?...

  1. #1

    Default Noob question: why the huge performance difference between these two queries?

    case1:
    select a.field1
    from foo a, (select field2 from bar where *condition*) b
    where a.field2 = b.field2

    vs.

    case2:
    select field1
    from foo a
    where a.field2 = any (select field2 from bar where *condition*)

    I'm simplifying a bit so it is possible that the actual specifics of
    *condition* are making a difference, but as far as my limited
    knowledge goes, I imagine not. case2 btw takes more than 10 times
    longer than case1.

    And aside from the specific answer to this question, could anyone
    recommend any online tutorials for query tuning?

    juv3nal Guest

  2. #2

    Default Re: Noob question: why the huge performance difference between these two queries?


    "juv3nal" <com> wrote in message
    news:googlegroups.com... 

    How does it compare to

    select a.field1
    from foo a
    join bar b on b.field2 = a.field2

    I was always told that in a more complicated query, you should get the
    number of records minimised as quickly as possible so that the select is
    working with as little as possible.

    I guess that there must be a performance tool out there somewhere. I
    primarily use SQL, which has this and is very good at helping to see where
    the problem lies, though it doesn't offer suggestions on how you can improve
    a query.



    Sean Guest

  3. #3

    Default Re: Noob question: why the huge performance difference between thesetwo queries?

    On 05.04.2007 10:42, Sean wrote: 
    >
    > How does it compare to
    >
    > select a.field1
    > from foo a
    > join bar b on b.field2 = a.field2[/ref]

    IMHO you are leaving out the condition, so it should be

    select a.field1
    from foo a
    join bar b on b.field2 = a.field2
    where *condition on b*

    But I agree, the first thing I'd try is to join vs. using inline views.

    Kind regards

    robert
    Robert Guest

  4. #4

    Default Re: Noob question: why the huge performance difference between these two queries?

    On 4 Apr, 23:36, "juv3nal" <com> wrote: 

    Never use a subselect where a join will do.

    Captain Guest

  5. #5

    Default Re: Noob question: why the huge performance difference between these two queries?


    "Robert Klemme" <com> wrote in message
    news:individual.net... 
    >>
    >> How does it compare to
    >>
    >> select a.field1
    >> from foo a
    >> join bar b on b.field2 = a.field2[/ref]
    >
    > IMHO you are leaving out the condition, so it should be
    >
    > select a.field1
    > from foo a
    > join bar b on b.field2 = a.field2
    > where *condition on b*
    >
    > But I agree, the first thing I'd try is to join vs. using inline views.
    >
    > Kind regards
    >
    > robert[/ref]


    Good point.



    Sean Guest

  6. #6

    Default Re: Noob question: why the huge performance difference between these two queries?

    Thanks, everyone.
    On further inspection, it seems I simplified things a bit much.
    There's actually some sum...group by & arithmetic involved which was
    the reason for the subquery.

    But aside from the specifics of this query, I'm more interested in if
    there are some good tutorials on query tuning in general.
    Principles like "Never use a subselect where a join will do." are
    good. Are there websites that have more of that kind of guideline?

    juv3nal Guest

  7. #7

    Default Re: Noob question: why the huge performance difference between thesetwo queries?

    juv3nal schrieb: 

    The most important tuning tip: use EXPLAIN.
    And read the manual pages on EXPLAIN. It took me several reads and
    re-reads to properly decode the EXPLAIN results.

    Caveat: EXPLAIN will tell you what the optimizer did, but it will not
    tell you why the optimizer arrived at a specific decision. (This is
    specific to mysql. Other database systems might return information about
    other aspects of query processing.)

    Regards,
    Jo
    Joachim Guest

  8. #8

    Default Re: Noob question: why the huge performance difference between these two queries?

    [Type your reply here]

    "juv3nal" <com> wrote: 

    There are some very useful books.
    The Art of SQL, Stéphane Faroult & Peter Robson, O'Reilly
    SQL Tuning, Dan Tow, O'Reilly
    Mysql Stored Procedure Programming, Steven Feuerstein & Guy Harrison, O'Reilly
    (last part is about tuning)

    Do not download these books in their CHM version from eMule, it is *piracy*.
    Please buy them instead.

    subtenante Guest

Similar Threads

  1. Noob question
    By Hill in forum Ubuntu
    Replies: 4
    Last Post: November 3rd, 07:26 PM
  2. performance difference in Webservice -'Normal' way
    By DraguVaso in forum ASP.NET Web Services
    Replies: 9
    Last Post: July 5th, 01:20 PM
  3. sorry noob question
    By the rebel in forum Macromedia Director 3D
    Replies: 3
    Last Post: September 26th, 12:17 PM
  4. Tab-delimited or CSV - Performance difference???
    By Laphan in forum ASP Database
    Replies: 5
    Last Post: August 30th, 06:05 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