Best Practice - mysql database query

[tdnxxx444]

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?

[The New Guy]

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

[tdnxxx444]

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.

[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

[Dan Grossman]

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.

[tdnxxx444]

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:

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?

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?

[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

[tdnxxx444]

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:

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:

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:

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:

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:

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.

[r937]

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

[Dan Grossman]

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...

[r937]

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

[Dan Grossman]

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?

[r937]

coldfusion

session variables, cached queries, url variables -- there are lots of techniques

[tdnxxx444]

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?