Professional Web Applications Themes

Should I use multiple joins or multiple where's to select matching data? What's fastest? - MySQL

I have a pretty simple website that deals with guitar tabs: table artist artist_ID, artist_name table title title_ID, title_name table tab tab_ID, artist_ID, title_ID, tab_text What's the most efficient (fastest) way I can display all the tabs in my database with one big query? I'd like it to be 1 query so I can sort based on artist_name. Should I use multiple joins or just use multiple where's? $query=" SELECT artist.artist_name, title.title_name, tab.tab_text FROM artist, title, tab WHERE artist.artist_ID = tab.artist_ID AND title.title_ID = tab.title_ID"; $results=mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($results)){ echo $row['artist_name']. " - ". $row['title_name']. " - ". ...

  1. #1

    Default Should I use multiple joins or multiple where's to select matching data? What's fastest?

    I have a pretty simple website that deals with guitar tabs:

    table artist
    artist_ID, artist_name

    table title
    title_ID, title_name

    table tab
    tab_ID, artist_ID, title_ID, tab_text


    What's the most efficient (fastest) way I can display all the tabs in
    my database with one big query? I'd like it to be 1 query so I can
    sort based on artist_name. Should I use multiple joins or just use
    multiple where's?

    $query="
    SELECT artist.artist_name, title.title_name, tab.tab_text
    FROM artist, title, tab
    WHERE artist.artist_ID = tab.artist_ID
    AND title.title_ID = tab.title_ID";

    $results=mysql_query($query) or die(mysql_error());

    while($row = mysql_fetch_array($results)){
    echo $row['artist_name']. " - ". $row['title_name']. " - ".
    $row['tab_text'];
    echo "<br />";
    }

    Also, is this the fastest way to echo out the query to a webpage?
    Thanks guys.

    Jared Guest

  2. #2

    Default Re: Should I use multiple joins or multiple where's to select matching data? What's fastest?

    Jared McQueen wrote: 
    Make sure you have indexes on artist_id and title_id, then:

    SELECT a.artist_name, t1.title_name, t.tab_text
    FROM tab t
    JOIN artist a ON a.artist_id = t.artist_id
    JOIN title t1 ON t1.title_id = t.title_id

    strawberry Guest

  3. #3

    Default Re: Should I use multiple joins or multiple where's to select matching data? What's fastest?


    "Jared McQueen" <com> wrote in message
    news:googlegroups.com... 
    Both the above query and Strawbery's one are JOINS. If you do an explain on
    them both they will look the same. the comma is another way of writing JOIN.
    However, Strawberry's version is more versatile as the JOINS can easily be
    replaced by LEFT JOINs if required. It is also (IMHO) easier to read.


    Paul Guest

  4. #4

    Default Re: Should I use multiple joins or multiple where's to select matching data? What's fastest?

    On Feb 27, 7:56 am, "Paul Lautman" <com>
    wrote: 








    >
    > Both the above query and Strawbery's one are JOINS. If you do an explain on
    > them both they will look the same. the comma is another way of writing JOIN.
    > However, Strawberry's version is more versatile as the JOINS can easily be
    > replaced by LEFT JOINs if required. It is also (IMHO) easier to read.[/ref]

    So are they functionally identical?

    strawberry Guest

  5. #5

    Default Re: Should I use multiple joins or multiple where's to select matching data? What's fastest?

    On 27 Feb, 13:50, "strawberry" <com> wrote: 

    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > So are they functionally identical?- Hide quoted text -
    >
    > - Show quoted text -[/ref]
     

    INNER JOIN and , (comma) are semantically equivalent in the absence of
    a join condition: both produce a Cartesian product between the
    specified tables (that is, each and every row in the first table is
    joined to each and every row in the second table).

    However, the precedence of the comma operator is less than than of
    INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins
    with the other join types when there is a join condition, an error of
    the form Unknown column 'col_name' in 'on clause' may occur.
    Information about dealing with this problem is given later in this
    section.


    So if you are only doing JOINs they are the same, as an EXPLAIN will
    show.

    Captain Guest

  6. #6

    Default Re: Should I use multiple joins or multiple where's to select matching data? What's fastest?

    On Feb 27, 9:13 am, "Captain Paralytic" <com>
    wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > INNER JOIN and , (comma) are semantically equivalent in the absence of
    > a join condition: both produce a Cartesian product between the
    > specified tables (that is, each and every row in the first table is
    > joined to each and every row in the second table).
    >
    > However, the precedence of the comma operator is less than than of
    > INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins
    > with the other join types when there is a join condition, an error of
    > the form Unknown column 'col_name' in 'on clause' may occur.
    > Information about dealing with this problem is given later in this
    > section.
    >
    > So if you are only doing JOINs they are the same, as an EXPLAIN will
    > show.[/ref]

    thanks for your responses... I'll try using the select strawberry gave
    above, and index the two ID columns.

    Jared Guest

  7. #7

    Default Re: Should I use multiple joins or multiple where's to select matchingdata? What's fastest?

    strawberry wrote:
     

    Sorry to interject, but I'm still trying to wrap my mind around
    this compound-select/join thing.

    I understand the first two lines will give me a table with these
    fields: a.artist_name, t1.title_name, t.tab_text.

    But I totally don't get what the JOIN lines do to that.
    Can anyone help me grok this?
    Sanders Guest

  8. #8

    Default Re: Should I use multiple joins or multiple where's to select matchingdata? What's fastest?

    Sanders Kaufman wrote: 
    >
    > Sorry to interject, but I'm still trying to wrap my mind around this
    > compound-select/join thing.
    >
    > I understand the first two lines will give me a table with these
    > fields: a.artist_name, t1.title_name, t.tab_text.
    >
    > But I totally don't get what the JOIN lines do to that.
    > Can anyone help me grok this?[/ref]

    Hi, Sanders,

    JOIN artist a ON a.artist_id = t.artist_id

    Get all rows in tab (aliased as t) which have an artist_id in table artist
     [/ref]

    Get all rows in tab (again aliased as t) which have a title_id found in
    title

    So, the whole thing searches for rows in tab (t) which have matching
    entries in artist (a) and title (t1).

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  9. #9

    Default Re: Should I use multiple joins or multiple where's to select matchingdata? What's fastest?

    Jerry Stuckle wrote: 
     [/ref][/ref]
     [/ref]
     

    I understand the *words*, but I can't envision the recordset
    that results. Maybe, let's dumb it down a little.

    Could you write a real, real simple JOIN and tell me what it
    does? No AS clause or nothin - just *simple*.

    I know there's a zillion tutorials out there, but none of them
    seem to sink with me.... and god knows I'm tryin!
    Sanders Guest

Similar Threads

  1. Coursebulider Multiple Select-Multiple Feedback
    By allankobernick in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: October 28th, 01:04 PM
  2. retrieving 'multiple select' data
    By Vbprog40 in forum Macromedia ColdFusion
    Replies: 5
    Last Post: August 5th, 05:50 PM
  3. What is the fastest way to add multiple child to aparent ?
    By mark val in forum Macromedia Director 3D
    Replies: 1
    Last Post: July 21st, 06:57 AM
  4. multiple inner joins
    By Anderson11983 in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 4th, 08:06 AM
  5. Help on Multiple JOINS
    By Bob Barrows in forum ASP Database
    Replies: 0
    Last Post: July 9th, 09:36 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