I may be missing something, but why not just do: SELECT CustomerName, ApplicationName, Status, COUNT(1) AS Count FROM LogMessage GROUP BY Status, CustomerName, ApplicationName with rollup;
which should return all the data you need in 1 query, which has got to run faster than 4 seperate queries... > -----Original Message----- > From: Stephen McMullan [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 02, 2003 12:59 PM > To: [EMAIL PROTECTED] > Subject: Large query techniques > > > Assuming that I had a database containing a single table used > to record an > audit trail of messages originated from customers and their > applications > like so: > > > CREATE TABLE `LogMessage` ( > `MessageID` int(11) NOT NULL auto_increment, > `CustomerName` varchar(100) default '', > `ApplicationName` varchar(100) default '', > `MessageText` text, > `Status` int(11) default '0', > PRIMARY KEY (`MessageID`) > ) TYPE=MyISAM; > > Each message could be in 4 different states (according to the > value of the > Status column) 0, 1, 2, 3 > > > > What would be the best way to query the table in order to > generate counts of > the number of messages in each state PER customer and application? > > I was thinking about: > > SELECT CustomerName, ApplicationName, '0' AS CountType, > COUNT(*) AS Count > FROM LogMessage WHERE Status = 0 GROUP BY CustomerName, > ApplicationName > UNION SELECT CustomerName, ApplicationName, '1' AS CountType, > COUNT(*) AS > Count FROM LogMessage WHERE Status = 1 GROUP BY CustomerName, > ApplicationName > UNION SELECT CustomerName, ApplicationName, '2' AS CountType, > COUNT(*) AS > Count FROM LogMessage WHERE Status = 2 GROUP BY CustomerName, > ApplicationName > UNION SELECT CustomerName, ApplicationName, '3' AS CountType, > COUNT(*) AS > Count FROM LogMessage WHERE Status = 3 GROUP BY CustomerName, > ApplicationName > > What would be the correct way to index the table? > > KEY `comboindex1` (`CustomerName`,`Status`), > KEY `comboindex2` (`CustomerName`,`ApplicationName`,`Status`), > KEY `status` (`Status`), > KEY `customername` (`CustomerName`), > KEY `applicationname` (`ApplicationName`) > > > I could have up to 30million rows in my table and am looking for some > fundamental techniques in order to query such a large table > in the manner > described above. > > Any pointers or help would be much appreciated. > > Regards > > Stephen McMullan > ANAM Wireless Internet Solutions > http://www.anam.com > +353 1 284 7555 > Castle Yard, Saint Patrick's Road, Dalkey, County Dublin, Ireland > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
