queries per page - how many?

[pir8ped]

Hi,

Until now, I have only had to run 1-3 queries per page. Is is reasonable to run say 50 queries for one page? I know I could just run a test, but I am at a design stage, so a rule of thumb answer would be helpful.

Thanks,

John Pedersen

[Rod Johnson]

No, unless you have a particularly low volume reporting app or the like.

Can you cache any of the data? Why do you need so many queries?

[pir8ped]

I am building an app which allows users to send messages to each other. I decided to try display the message listing like GMail, which has threaded messages, and a way of displaying them that I like:

John, Rod (2) Number of queries 1:48pm
etc.

So the message listing requires, for each thread:

The usernames of each person who has sent a message in this thread.
The number of messages in the thread.
The subject of the first message in the thread.
The date/time the last message in the thread was sent.

The initial message listing might show 20, or perhaps up to 50 lines of thread headers.

I have a table:

CREATE TABLE `messages` (
`messageID` int(11) unsigned NOT NULL auto_increment,
`threadID` int(11) unsigned default NULL,
`fromUserName` varchar(50) default NULL,
`toUserName` varchar(50) default NULL,
`subject` varchar(255) default NULL,
`body` text,
`dateSent` datetime default NULL,
`isRead` tinyint(4) default '0',
...indexes...



I just can't see how to get a listing like this without denormalising the db, or using several queries, something like:

select threadID, dateSent from messages where toUserName = ''

then for each threadID:

select COUNT(*) from messages where threadID = ''

select fromUserName, subject from messages where threadID = '' order by dateSent LIMIT 1


This isn't really a Spring topic, but Rod, you did ask why I needed so many queries. I had always assumed maybe 1-3 queries a page was appropriate, but I noticed some people on the mysql forum working with 300 queries for a page. It made me question previous assumptions.

John

[tnelson]

I think you'd be better off just selecting all the messages in a thread and parsing through them yourself. It'd be a lot quicker than hitting the database for every little thing.

[pir8ped]

I might be looking at 50 threads, so you are suggesting I parse through all the messages of each of the 50 threads? Maybe...

But after the last post I made, I realised that of course this forum displays messages in the same way as GMail, so I took a look at the table structure.in the source files. I think my db design was rather naive. I think working with something like this should be OK:

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 ;

CREATE TABLE `threads` (
`threadID` int(11) NOT NULL auto_increment,
`threadSubject` varchar(200) default NULL,
`threadStarter` varchar(50) default NULL,
`threadRecipient` varchar(50) default NULL,
`timeLastPost` timestamp(14) NOT NULL,
PRIMARY KEY (`threadID`)
) TYPE=InnoDB ;

John