Fellow MySQLers,
I have a query that performs poorly and I was hoping that someone could
make a recommendation as to how to
improve it's performance. The query involves a join between the
fs_syslog table (~700K rows) and the hosts
table (~100 rows). I am using MySQL 5.0.41 running on CentOS 4.4 (2.6
Kernel). There is only 1 index on the
fs_syslog besides the primary key:
ALTER TABLE fs_syslog ADD INDEX fs_syslog_1 (src_time DESC);
Here is the table definition for fs_syslog:
CREATE TABLE `fs_syslog` (
`id` bigint(20) NOT NULL auto_increment,
`facility` int(11) default NULL,
`severity` int(11) default NULL,
`host` varchar(255) default NULL,
`src_ip` varchar(255) default NULL,
`src_time` timestamp NULL default NULL,
`srv_ip` varchar(255) default NULL,
`srv_time` timestamp NULL default NULL,
`tag` varchar(255) default NULL,
`content` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `fs_syslog_1` (`src_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And here it is for the hosts table:
CREATE TABLE `hosts` (
`hostid` bigint(20) unsigned NOT NULL default '0',
`host` varchar(64) NOT NULL default '',
`dns` varchar(64) NOT NULL default '',
`useip` int(11) NOT NULL default '1',
`ip` varchar(15) NOT NULL default '127.0.0.1',
`port` int(11) NOT NULL default '10050',
`status` int(11) NOT NULL default '0',
`disable_until` int(11) NOT NULL default '0',
`error` varchar(128) NOT NULL default '',
`available` int(11) NOT NULL default '0',
`errors_from` int(11) NOT NULL default '0',
PRIMARY KEY (`hostid`),
KEY `hosts_1` (`host`),
KEY `hosts_2` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Here is the query. As you can see there is an ORDER BY which is the
culprit. I would like to get rid of the ORDER BY
and rely on the index (DESC) to give me back the most recent 10 rows
from the join, but currently DESC doesn't
work when creating the index. Removing the ORDER BY makes the query
return almost instantaneously,
but when the ORDER BY is in place, they query takes anywhere from 3 secs
to 20 secs (but usually around
3 secs). The fs_syslog table only contains 700K rows now but it will
eventually contains tens of millions of rows.
I am trying to return the 10 most recently occurring syslog messages
which is the reason for the LIMIT clause.
Any help would be greatly appreciated.
SELECT fsys.facility, fsys.severity, h.host, fsys.src_ip, fsys.src_time,
fsys.content, fsys.tag
FROM fs_syslog fsys, hosts h
WHERE fsys.src_ip=h.ip
AND h.status <> 4
AND h.hostid in ({about 40 entries here})
AND h.host in ({about 40 entries here}))
AND fsys.src_time >= DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6 HOUR)
ORDER BY fsys.src_time DESC
LIMIT 0,10;
Thanks,
Mark Ponthier
