complicated select!

[John Pedersen]

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

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

[Bill Karwin]

Re: complicated select!

John Pedersen wrote:
[color=blue]
> ...in one select, if possible![/color]

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.

[John Pedersen]

Re: complicated select!

Bill Karwin <bill@karwin.co m> wrote in message news:<ce660a01a br@enews1.newsg uy.com>...[color=blue]
> John Pedersen wrote:
>[color=green]
> > ...in one select, if possible![/color]
>
> 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.[/color]

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

[Bill Karwin]

Re: complicated select!

John Pedersen wrote:[color=blue][color=green]
>>Why in one select?[/color]
>
> 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...[/color]

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::FillInFor m, 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.

[Bill Karwin]

Re: complicated select!

Bill Karwin wrote:[color=blue]
> 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.[/color]

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.

[John Pedersen]

Re: complicated select!

OK, got it - I will use however many hits I need to do it most simply.
I am keeping the connections alive.

Thanks for the responses.

John

[John Pedersen]

Re: complicated select!

OK, got it - I will use however many hits I need to do it most simply.
I am keeping the connections alive.

Thanks for the responses.

John