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]
