It looks like u dont have index on fsys.src_ip and host.ip, please create
index on these two columns, and also do a explain of ur query, u will know ,
where the problem is.
regards
anandkl
On 8/13/07, Mark Ponthier <[EMAIL PROTECTED]> wrote:
>
> 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
>
>
>
>
