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:[EMAIL PROTECTED]
Sent: mardi 26 avril 2005 05:49
To: mysql@lists.mysql.com
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
