List:General Discussion« Previous MessageNext Message »
From:Michael Gale Date:April 26 2005 3:49am
Subject:Index help ?
View as plain text  

	I have the following table setup:

ID    hostname    facility    priority    date    message

ID is auto incrementing.

This is used to store all of the syslog messages, currently there are 
over 7 million:

The following query takes forever:
Select machine,count(*) from syslog WHERE date1 > (NOW() - INTERVAL 24 
hour) AND message LIKE 'sshd%' GROUP BY machine;

I have created the following indexs but when I use Explain it says that 
the query has to search all the rows:

  datehostfacility              1   date1         A                352489
  datehostfacility              2   machine       A               1409956
  datehostfacility              3   facility      A               1409956

  datemesghost                  1   date1         A                640889
  datemesghost                  2   message(15)   A               7049783
  datemesghost                  3   machine       A               7049783
  datemesghost                  4   facility      A               7049783
  datemesghost                  5   priority      A               7049783

What would the proper index be ?

Index help ?Michael Gale26 Apr
  • RE: Index help ?mathias fatene26 Apr