Announcement Announcement Module
Collapse
No announcement yet.
queries per page - how many? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • queries per page - how many?

    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

  • #2
    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?

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Comment


        • #5
          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

          Comment

          Working...
          X