Professional Web Applications Themes

Join vs subselect - MySQL

Hi, is there a difference in performance between SELECT a.* FROM a INNER JOIN b USING (some_key) and SELECT * FROM a WHERE EXISTS (SELECT * FROM b WHERE a.some_key = b.some_key) ? Regards, André...

  1. #1

    Default Join vs subselect

    Hi,

    is there a difference in performance between
    SELECT a.* FROM a INNER JOIN b USING (some_key)
    and
    SELECT * FROM a WHERE EXISTS (SELECT * FROM b WHERE a.some_key =
    b.some_key)
    ?

    Regards,
    André

    André Guest

  2. #2

    Default Re: Join vs subselect

    André Hänsel schrieb: 

    Likely. The mysql optimizer doesn't try to optimize things that you
    could write more efficiently by yourself (at least that's what I read
    between the lines of the docs and guiding principles of the optimizer
    design - I may be wrong).
    Try EXPLAIN <sql-statement> for each of the two statements; if the
    results are the same, the optimizer will be equally efficient (or
    inefficient, as the case may be); if they are different, you most likely
    will have performance differences.

    HTH
    Jo
    Joachim Guest

  3. #3

    Default Re: Join vs subselect

    On 2 Mai, 23:43, Joachim Durchholz <org> wrote: 
    >
    > Likely. The mysql optimizer doesn't try to optimize things that you
    > could write more efficiently by yourself (at least that's what I read
    > between the lines of the docs and guiding principles of the optimizer
    > design - I may be wrong).
    > Try EXPLAIN <sql-statement> for each of the two statements; if the
    > results are the same, the optimizer will be equally efficient (or
    > inefficient, as the case may be); if they are different, you most likely
    > will have performance differences.[/ref]

    Can I be sure that EXPLAIN gives me _all_ information about how the
    query is performed? So when the output of EXPLAIN, the result and the
    state of the system (table content, memory utilization, etc.) are the
    same, two queries are identically fast?

    André Guest

Similar Threads

  1. HELP with SQL subselect
    By starman7@hotmail.com in forum MySQL
    Replies: 3
    Last Post: December 1st, 02:20 AM
  2. subselect not responding
    By Bill in forum Macromedia Freehand
    Replies: 3
    Last Post: March 7th, 02:04 PM
  3. Replies: 2
    Last Post: September 18th, 09:59 PM
  4. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 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