SitePoint Sponsor |
|
User Tag List
Results 1 to 14 of 14
-
Jul 25, 2006, 13:13 #1
- 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?
-
Jul 25, 2006, 14:43 #2
- 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
-
Jul 25, 2006, 16:29 #3
- 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.
-
Jul 25, 2006, 16:43 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Jul 25, 2006, 18:05 #5
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- 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.
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Aug 8, 2006, 09:17 #6
- Join Date
- Mar 2006
- Posts
- 81
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
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 }
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 }
-
Aug 8, 2006, 09:34 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Aug 11, 2006, 14:30 #8
- Join Date
- Mar 2006
- Posts
- 81
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
I have two tables:
Code:account -------- account_id username email message --------- message_id from_account_id to_account_id subject body
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 "; }
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 }
Code:function getMessageForm($to_account_id, $subject, $body) { to: getUsernameFromAccountId($to_account_id); subject : $subject body : $body }
Code:function getUsernameFromAccountId($account_id) { SELECT username FROM accounts WHERE account_id=$account_id }
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.
-
Aug 11, 2006, 15:34 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by tdnxxx444
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.
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:
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.
and as far as classes are concerned, good luck with them, 'cause i have no idea what those are
-
Aug 11, 2006, 16:59 #10
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- 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...
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Aug 11, 2006, 17:03 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Aug 11, 2006, 17:04 #12
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Aug 11, 2006, 17:09 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
coldfusion
session variables, cached queries, url variables -- there are lots of techniques
-
Aug 11, 2006, 18:47 #14
- 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