Ben It would appear that the deletion of rows may be a problem. After deleting rows older than 6 months, do you optimize the table?
As I understand it, mysql does not delete delete, only marks a bit for every row thats deleted. That way, delete speed is fast. However, it slows down queries and inserts. I've tested this my deleting half of the rows in a table. The before and after file size size's are equal. I don't think you need the ORDER BY Account clause. The GROUP BY Account will automatically sort it. I read it somewhere. David > -----Original Message----- > From: Ben Holness [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, August 27, 2002 1:20 AM > To: Mysql z_mailing > Subject: Indexing question > > > Hi all, > > I would like to create an index to speed up the following query: > > SELECT Account, Status, count(*) From MessageStatus WHERE sentDate > > '(variable)' AND sentDate < '(variable)' GROUP BY > Account,Status ORDER BY > Account > > sentDate is a timestamp(14), Account and Status are both varchars. > > The table also contains another couple of columns and already > has an index > on sentDate. > > So two questions: > > 1. What index should I create (my original guess was just > sentDate, but now > I am thinking sentDate(8),Account,Status) > > 2. Does the fact that I created the sentDate index, which is > not being used > as it does not speed up the query, detriment the performance > significantly? > Should I remove that index as it is not being used? > > The table currently has around 800,000 entries in it and > grows by between > 4,000 and 100,000 entries a day. > > Entries are deleted once they are 6 months old. > > Many thanks, > > Ben > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
