RE: Index help ?

[mathias fatene]

I think the second can be better (more different values). But it
contains almost the same data than the table. 
Try :
	explain Select machine,count(*) from syslog WHERE date1 > (NOW()
- INTERVAL 24 
hour) AND message LIKE 'sshd%' GROUP BY machine;

But an index with(date1, message, machine)  sould be sufficient.

Best Regards
--------------------
Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-----Original Message-----
From: Michael Gale [mailto:michael.gale@stripped] 
Sent: mardi 26 avril 2005 05:49
To: mysql@stripped
Subject: Index help ?


Hello,

	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 ?

Michael

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1