Professional Web Applications Themes

Easier way then to use a join here? - MySQL

I use the following select statement that correctly retrieves the data I want: SELECT * FROM userpref AS pref JOIN userpref AS pref2 ON pref2.userid=pref.userid WHERE ( pref.questionid = '30' and pref.answer = '7507') and( pref2.questionid = '41' and pref2.answer = '9108' ) The problem is that some of my select statements get rather complicated and I use a lot of self-joining to the userpref table above, perhaps a dozen or so self joins. This causes the select statements to really sloooow down, taking a few minutes in some cases. Since this is used by a website, this is not ...

  1. #1

    Default Easier way then to use a join here?

    I use the following select statement that correctly retrieves the data
    I want:

    SELECT * FROM userpref AS pref
    JOIN userpref AS pref2 ON pref2.userid=pref.userid
    WHERE ( pref.questionid = '30' and pref.answer = '7507')
    and( pref2.questionid = '41' and pref2.answer = '9108' )

    The problem is that some of my select statements get rather complicated
    and I use a lot of self-joining to the userpref table above, perhaps a
    dozen or so self joins. This causes the select statements to really
    sloooow down, taking a few minutes in some cases. Since this is used by
    a website, this is not a good solution.
    I thought I could improve performance if I were to trash the joins
    and somehow do all the logic in the WHERE statement as:

    SELECT * FROM userpref AS pref
    WHERE ( pref.questionid = '30' and pref.answer = '7507')
    and ( pref.questionid = '41' and pref.answer = '9108' )

    Of course this doens't work, as the same questionid is referenced
    twice, but my question is, is there some syntax here that would give
    the same result as the join above but without using the join and have
    the logic in the where statement alone?
    Thanks

    ukr_bend@yahoo.com Guest

  2. #2

    Default Re: Easier way then to use a join here?

    [email]ukr_bend[/email] wrote:
    > SELECT * FROM userpref AS pref
    > WHERE ( pref.questionid = '30' and pref.answer = '7507')
    > and ( pref.questionid = '41' and pref.answer = '9108' )
    >
    > Of course this doens't work, as the same questionid is referenced
    > twice, but my question is, is there some syntax here that would give
    > the same result as the join above but without using the join and have
    > the logic in the where statement alone?
    The easiest solution is to use OR instead of AND:

    SELECT * FROM userpref AS pref
    WHERE ( pref.questionid = '30' and pref.answer = '7507' )
    OR ( pref.questionid = '41' and pref.answer = '9108' )

    Of course this produces a result set of more than one row, but then you
    can loop through the result set in your application code and format it
    the way you want.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Easier way then to use a join here?

    I use the following select statement that correctly retrieves the data
    I want:

    SELECT * FROM userpref AS pref
    JOIN userpref AS pref2 ON pref2.userid=pref.userid
    WHERE ( pref.questionid = '30' and pref.answer = '7507')
    and( pref2.questionid = '41' and pref2.answer = '9108' )

    The problem is that some of my select statements get rather complicated
    and I use a lot of self-joining to the userpref table above, perhaps a
    dozen or so self joins. This causes the select statements to really
    sloooow down, taking a few minutes in some cases. Since this is used by
    a website, this is not a good solution.
    I thought I could improve performance if I were to trash the joins
    and somehow do all the logic in the WHERE statement as:

    SELECT * FROM userpref AS pref
    WHERE ( pref.questionid = '30' and pref.answer = '7507')
    and ( pref.questionid = '41' and pref.answer = '9108' )

    Of course this doens't work, as the same questionid is referenced
    twice, but my question is, is there some syntax here that would give
    the same result as the join above but without using the join and have
    the logic in the where statement alone?
    Thanks

    ukr_bend@yahoo.com Guest

  4. #4

    Default Re: Easier way then to use a join here?

    Thanks Bill. Could be the solution. But what I need are rows that only
    meet all 4 conditions of the where statement. But like you said,
    perhaps retrieve everything then do the grunt work in the app.

    ukr_bend@yahoo.com Guest

  5. #5

    Default Re: Easier way then to use a join here?

    Not seeing any details about the table layout or what the end goal is,
    I can only speculate and give you a method that I would choose.

    It appears that this is a table that stores user preferences and then
    does something on the display based on those preferences.

    create table pref (uid integer, prefq integer, prefanswer integer) !!
    I prefer to use integers where value will always be a number.
    create table userinfo (uid integer, username varchar, fname varchar,
    lastname varchar....)
    questiontbl
    answtbl

    insert into pref values (1,30,7507)
    insert into pref values (1,41,9108)
    insert into pref values (1,45,10032)
    insert into pref values (2,30,7506)
    insert into pref values (2,41,9107)
    insert into pref values (2,45,10031)

    now I can select/join them all together in one query.

    select a.uid, b.prefq, c.prefanswer
    from pref a, questiontbl b,anstbl c
    where a.uid=123 and b.prefq=a.prefq and c.prefanswer=a.prefanswer

    Now, all you need is the uid (user id) and you can retrieve all of the
    questions and all of the answers in one query. And this would be very
    fast as these "primary keys" would/could be indexed making retrieval
    very fast.

    One thing you will need in any case like this would be a way to ensure
    that answer xyz goes with question 123 etc...

    As always, YMMV

    onedbguru@firstdbasource.com Guest

  6. #6

    Default Re: Easier way then to use a join here?

    [email]ukr_bend[/email] wrote:
    > Thanks Bill. Could be the solution. But what I need are rows that only
    > meet all 4 conditions of the where statement. But like you said,
    > perhaps retrieve everything then do the grunt work in the app.
    There's a HAVING COUNT technique that I have seen used, but I have
    always thought it is kind of delicate and too easy to get wrong.

    SELECT pref.userid FROM userpref AS pref
    WHERE ( pref.questionid = '30' and pref.answer = '7507' )
    OR ( pref.questionid = '41' and pref.answer = '9108' )
    GROUP BY userid
    HAVING COUNT(*) = 2

    Change the value 2 to another value, matching the number of questionid
    terms you have in your WHERE clause.

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. [PHP] Is there an easier way?
    By David Nicholson in forum PHP Development
    Replies: 0
    Last Post: August 1st, 01:08 AM
  3. Is there an easier way?
    By Ryan A in forum PHP Development
    Replies: 0
    Last Post: August 1st, 01:03 AM
  4. Tell me how it was easier???
    By Delores Highsmith in forum Macromedia Freehand
    Replies: 3
    Last Post: July 22nd, 05: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