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]
