473,323 Members | 1,589 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

complicated select!

Hi,

I have this table:

CREATE TABLE `messages` (
`messageID` int(11) unsigned NOT NULL auto_increment,
`threadID` int(11) unsigned,
`fromUserName` varchar(50) default NULL,
`toUserName` varchar(50) default NULL,
`subject` varchar(255) default NULL,
`body` text,
`dateSent` datetime,
`isRead` tinyint(4) default '0',
PRIMARY KEY (`messageID`),
KEY `fromUserName` (`fromUserName`),
KEY `toUserName` (`toUserName`),
KEY `threadID` (`threadID`)
) TYPE=InnoDB ;

which is planned so that I can use threaded messages in a forum type
of app.

Here is what I would like to get hold of in one select, if possible!

Given a userName and a threadID,

fromUserName of the first message in the thread
subject of the first message in the thread
dateSent of the last message in the thread
boolean - if any of the messages in the thread are unread where
userName == messages.toUserName

Is this possible? I have mySql 4.1 so subselects are OK. I could do it
with another table called threads and a bit of denormalisation, but
would this be necessary?

Any suggestions would be very welcome,

Thanks,

John Pedersen
Jul 20 '05 #1
4 1961
John Pedersen wrote:
...in one select, if possible!


Why in one select? You're going to spend many hours getting it right
and debugging, and many more hours if you ever need to modify it to get
another attribute of your message threads. It would be such a
time-saver (in terms of programming time) to do it in two or three queries.

Regards,
Bill K.
Jul 20 '05 #2
Bill Karwin <bi**@karwin.com> wrote in message news:<ce*********@enews1.newsguy.com>...
John Pedersen wrote:
...in one select, if possible!


Why in one select? You're going to spend many hours getting it right
and debugging, and many more hours if you ever need to modify it to get
another attribute of your message threads. It would be such a
time-saver (in terms of programming time) to do it in two or three queries.


I am thinking that people will often be looking at a list of their
messages, and a page full of headers would take a lot of hits to the
db if I need to make 2-3 queries per header. And there may be several
pages of headers per user...

John
Jul 20 '05 #3
John Pedersen wrote:
Why in one select?


I am thinking that people will often be looking at a list of their
messages, and a page full of headers would take a lot of hits to the
db if I need to make 2-3 queries per header. And there may be several
pages of headers per user...


The work of 2-3 simple queries vs. the work of one very complex query
that fetches the same results is virtually the same (the degenerate case
would be a UNION of those 2-3 simple queries), or so close that the rest
of the work to prepare the web page is much more significant. Unless
you're doing something strange, like opening a new connection to the
database for each query.

I did some profiling of my current application project using
Apache::DProf, and I found that 80% of the processing time to prepare a
page is spent in HTML::FillInForm, and the slowness of a page is
proportional to the complexity and length of the HTML, not the number of
queries I use for the page. The MySQL data access, though it may
require 6-10 queries per page, is tiny. When I add another query for
some more dynamic data (a pulldown list for example) it doesn't slow
down response time perceptibly.

If you're concerned about performance, do some tests with a profiler and
figure out where the bottleneck is, instead of assuming that it's in the
database queries.

Regards,
Bill K.
Jul 20 '05 #4
Bill Karwin wrote:
The work of 2-3 simple queries vs. the work of one very complex query
that fetches the same results is virtually the same (the degenerate case
would be a UNION of those 2-3 simple queries), or so close that the rest
of the work to prepare the web page is much more significant.


I didn't write that very well, and it reads as a pretty dumb
generalization. Sorry about that.

What's more relevant is: even if the work of 3 queries costs 3 times as
much as one complex query, it's not a problem if it still represents a
tiny fraction of the cost of preparing the web page. There's no sense
being penny wise and pound foolish.

Regars,
Bill K.
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Rene | last post by:
Hello If I a user table and a item table has - in both a incremental value is led (ID) - and I beside it in an order table all sales the user lead, how I can all items to be indicated be able,...
3
by: Narine | last post by:
Hi All, I need to write one complicated update statement and I'm looking at maybe finding a simpler way to do it. I have 2 tables: 1.Photo Table PhotoID FileName 1 111.jpg
3
by: Jason | last post by:
I'm having some trouble coming up with the correct select statement. Lets say I have the following two tables: ---------------- ---------------- Orders ...
10
by: jqq | last post by:
SQL2K on W2Kserver I need some help revamping a rather complicated query. I've given the table and existing query information below. (FYI, changing the database structure is right out.) The...
2
by: gimme_this_gimme_that | last post by:
Can this statement be translated into DB2 ? CREATE VIEW RELEASE_REPORTS AS (SELECT RI.GROUP_ID AS GROUP_ID, RI.RELEASE_ID AS RELEASE_ID, RI.TARGET_REL_ID, RI.REL_DROP, B.DROPCOUNT ,...
5
by: Norma | last post by:
I am trying to make a query pull data from between the dates I enter in the parameter but also look back 'in time' to see where 2 other fields have null values, and only pull data into the query if...
26
by: Jeff | last post by:
Ok gang. Here is something complicated, well, at least to me anyway. Using Access DB I have a table in my DB called members. In that table, I have 2 tables I will be using "username" and...
4
by: Matthew Crouch | last post by:
i suck so much that i don't even know if this is a JOIN or a subquery or who-knows what. Here's the idea: I want to select two things at the same time (form one table) average for columnX and...
2
by: Nick | last post by:
Hi, I have two tables Trade table and Cons table. Records are inserted in both the tables independent of each other. There are fields like Exc_Ref, Qty, Date in both the tables. I need to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.