SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best Practice - mysql database query

    Lets say I have a messaging section of my site. In this site, there are various SELECT queries:

    - Query #1 SELECTS all the message body details, and whoever wrote the message, based on message_id
    i.e. SELECT message.body, message.from_account_id
    FROM message, account
    WHERE message.message_id = $message_id
    AND account.account_id

    - Query #2 SELECTS just the message body, based on message_id
    i.e. SELECT message.body
    FROM message
    WHERE message.message_id = $message_id

    - Query #3 SELECTS all messages from a certain account_id
    i.e. SELECT message.body, message.from_account_id
    FROM message, account
    WHERE message.from_account_id = $account_id

    The queries above are just examples I thought off the top of my head.
    Now my question is, is there a way to generalize all these queries, so that I don't have to keep on writing queries over and over, or do I have write these different queries as there are all on a different case by case basis?

  2. #2
    Put your best practices away. The New Guy's Avatar
    Join Date
    Sep 2002
    Location
    Canada
    Posts
    2,087
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    I really recommend this video from Google TechTalks it gives alot of tips for best ways with mysql.

    http://video.google.com/videoplay?do...ogle+TechTalks
    "A nerd who gets contacts
    and a trendy hair cut is still a nerd"

    - Stephen Colbert on Apple Users

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the video. I watched most of it, however, it showed mainly how to optimize queries so that it'd be faster. However, I was seeing how to do queries so they are easier to maintain.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    best practice: figure out what you're doing and write the leanest, meanest, most appropriate query for each instance

    SELECT only those columns that you need, only from the tables that you need

    this means most queries will be unique

    trust me, this makes them easier to maintain
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    When I used Hibernate in Java, the queries it would run were soooo bad, the whole application was incredibly slow. I hand write the queries into the service classes now.

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    best practice: figure out what you're doing and write the leanest, meanest, most appropriate query for each instance

    SELECT only those columns that you need, only from the tables that you need

    this means most queries will be unique

    trust me, this makes them easier to maintain
    r937, are you sure? Lets say I have some tables:

    Code:
    item
    ------
    item_id
    item_title
    item_price
    
    
    item_account
    --------------
    item_id
    account_id
    
    
    function getItemTitleFrmItemId($item_id)
    {
       SELECT item_title
       FROM item
       WHERE item.item_id=$item_id
    }
    
    function getItemTitleFrmAccountId
    {
       SELECT item_title
       FROM item, item_account
       WHERE item_account.account_id=$account_id
       AND item.item_id = item_account.item_id
    }
    and what if we just wanted to get the item_price?

    Code:
    function getItemPriceFrmItemId($item_id)
    {
       SELECT item_price
       FROM item
       WHERE item.item_id=$item_id
    }
    
    function getItemPriceFrmAccountId
    {
       SELECT item_price
       FROM item, item_account
       WHERE item_account.account_id=$account_id
       AND item.item_id = item_account.item_id
    }
    I don't know. It seems unnecessarily repetive and harder to maintain. Or could there possibly be a better way?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i did not suggest writing a function for each column, if that's what you think i meant

    even without functions, i would not suggest writing a query for each column

    in fact, don't write anything on the chance that you might need it (you'll end up writing O(n!) queries (or something like that -- i forget the actual combinatoric formula)

    write the query at the point where the app needs the data

    the app's business requirements will determine which column(s) you need
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i did not suggest writing a function for each column, if that's what you think i meant

    even without functions, i would not suggest writing a query for each column

    in fact, don't write anything on the chance that you might need it (you'll end up writing O(n!) queries (or something like that -- i forget the actual combinatoric formula)

    write the query at the point where the app needs the data

    the app's business requirements will determine which column(s) you need
    r937, gotcha. Thanks for the insighful replies. I have an actual real world example though, if you can help me with it.

    I have two tables:

    Code:
    account
    --------
    account_id
    username
    email
    
    
    message
    ---------
    message_id
    from_account_id
    to_account_id
    subject
    body
    Now lets say a user has a listing of messages in his box. He clicks on one of the messages, and it pops up another window to show the
    full message with who its from. So we call this function:

    Code:
    function getMessageFromMessageId($message_id)
    {
       SELECT message.message_id, message.from_account_id, message.to_account_id, message.subject, message._body, account.username 
       FROM message, account
       WHERE message.message_id = $message_id 
       AND account.account_id = messages.message_from_account_id ";
    }
    The user then clicks on reply, to reply to the respective message. So we call another function to reply to the message. This function
    quotes the original message in the reply message body. In order to quote it, we have to get the body of the original message. So we call this
    function:

    Code:
    function getMessageBody($message_id)
    {
       SELECT message.body
       FROM mesage
       WHERE message.message_id = $message_id 
    }
    Then after it gets the original message and quotes it, it produces a message form for the user to reply to:

    Code:
    function getMessageForm($to_account_id, $subject, $body)
    {
       to: getUsernameFromAccountId($to_account_id);
       subject : $subject
       body : $body
    }
    which uses getUsernameFromAccountId(), since there are other places in my app where I have to derive the username when given the account_id:

    Code:
    function getUsernameFromAccountId($account_id)
    {
       SELECT username
       FROM accounts
       WHERE account_id=$account_id
    }
    Should I have written out all these distinct queries based on the requirements or is there a better way? In the first function, to display who the message is from, I could have, instead of using a join, just SELECT from the message table, and use getUsernameFromAccountId() but is the better way?

    Also, please note that this is just an example and I just put the, into functions for simplicity sake and to try to illustrate what I'm try to convery
    as clearly as possible. This could have actually been put into the class methods in a data access layer.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tdnxxx444
    Now lets say a user has a listing of messages in his box. He clicks on one of the messages, and it pops up another window to show the
    full message with who its from. So we call this function:
    so far so good, sort of

    after all, you had to have already run a query to get his list of messages, and that's where you should already have retrieved who each message is from -- in fact, the only thing you should be missing is the body of the message clicked on (whoch you would not retrieve when retrieving a list of messages)

    The user then clicks on reply, to reply to the respective message. So we call another function to reply to the message. This function
    quotes the original message in the reply message body. In order to quote it, we have to get the body of the original message.
    nope, you would've got that in order do display it so that he can reply to it

    so i just saved you one complete call

    Then after it gets the original message and quotes it, it produces a message form for the user to reply to: ... which uses getUsernameFromAccountId(), since there are other places in my app where I have to derive the username when given the account_id:
    fine, if there are other places where you need that, but this isn't one of them, you already know who it's from!!


    Also, please note that this is just an example and I just put the, into functions for simplicity sake and to try to illustrate what I'm try to convery
    as clearly as possible. This could have actually been put into the class methods in a data access layer.
    i appreciate that this is just an example, but i would advise you to look carefully at everything you do, so as not to call the database needlessly

    and as far as classes are concerned, good luck with them, 'cause i have no idea what those are
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Are you giving advice for offline applications? Because I was reading this thread and not following your advice. Each action here -- listing messages, viewing an individual message, replying to a message, are separate executions of the scripts, whatever language it's in. The results of the query to build the list of messages are not available to the page that shows an individual message clicked on...

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, dan, you do realize that i was giving advice based on the subject (Best Practice - mysql database query) and i will stick by it -- don't call the database more often than you have to

    if you develop an app that completely forgets where it is, then yeah, you have to call the database fresh every time you execute a script

    the price you pay for modularity, i suppose
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    if you develop an app that completely forgets where it is, then yeah, you have to call the database fresh every time you execute a script

    the price you pay for modularity, i suppose
    Websites built in PHP, Perl, Python, Ruby... what languages do you develop websites in that don't forget where they are? Isn't this the nature of HTTP?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    coldfusion

    session variables, cached queries, url variables -- there are lots of techniques
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gotcha. So in the example I showed, I should have persisted my data on each database call with session variables instead of unnecessarily calling the database on each new HTTP request? This would have the been the best practice?


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
  •