Sorry,
That was really hard to read. Here it is again:
mysql> explain 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
(80,75,81,78,79,76,24,25,39,33,34,35,16,60,37,38,54,26,27,28,29,30,31,32
,51,59,58,13,40,41,45,46,87,82,88,84,85,86,83,73,68,74,70,71,72,69,61,66
,63,67,64,65)
-> AND h.host
in('FSBOS2950','FSBOSDC','FSBOSFireScope','FSBOSFirewall','FSBOSRTR','FS
BOSSQL','CA_CoLoc','CA_LAB_PIX','FS_CoLo_2950','FSDALPIX','FSDALSWT01','
FSDALSWT02','FSDEV01_10_11','fsdev_Oracle','FSTESTPIX','FSWIN2K3-IIS','F
SWINSRV01','FSWINSRV02','FS_Dallas_2950','Phone Switch','RedMoon
Gateway','XO
Gateway','FS_CoLo_2950','FS_Dallas_2950','FS_Dev1014','FS_Offc_Printer',
'FS_Offc_ScanPrnter','Demo-Cisco-1','Demo-Cisco-2','Demo-Linux','Demo-pi
x','Demo-Switch-1','Demo-Switch-2','Demo-Windows','Phone
Switch','RedMoon Gateway','XO
Gateway','FSDEV01_10_11','FS_Dallas_2950','FS_Dev1014','FS_Offc_Printer'
,'FS_Offc_ScanPrnter','FSDTC2950','FSDTCDC','FSDTCFireScope','FSDTCIntra
net','FSDTCPIX','FSDTCRTR','FSDTCSQL','FSENG3560','FSENGDC','FSENGFireSc
ope','FSENGIntranet','FSENGPIX','FSENGRTR','FSENGSQL','FSSALES01','FSSAL
ESDB','FSSALESFW','FSSALESPhone','FSSALESSWITCH','FSSALESWEB')
-> AND fsys.src_time >= DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6
HOUR)
-> ORDER BY fsys.src_time DESC
-> limit 0,10;
+----+-------------+-------+-------+-------------------------+----------
---+---------+------+-------+-------------------------------------------
---+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+-------+-------+-------------------------+----------
---+---------+------+-------+-------------------------------------------
---+
| 1 | SIMPLE | fsys | range | fs_syslog_1,fs_syslog_2 |
fs_syslog_1 | 5 | NULL | 23664 | Using where; Using temporary;
Using filesort |
| 1 | SIMPLE | h | ALL | PRIMARY,hosts_1,hosts_2 | NULL
| NULL | NULL | 96 | Using where
|
+----+-------------+-------+-------+-------------------------+----------
---+---------+------+-------+-------------------------------------------
---+
2 rows in set (0.00 sec)
Thanks,
Mark Ponthier
-----Original Message-----
From: Mark Ponthier [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 14, 2007 9:47 AM
To: mysql@lists.mysql.com
Subject: RE: Slow query involving ORDER BY
I've added both indexes but don't see an improvement in speed. Below are
the the descriptions of each table and the explain plan:
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`),
KEY `hosts_3` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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`),
KEY `fs_syslog_2` (`src_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Below is the explain plan:
id select_type table type possible_keys
key key_len ref rows
Extra
1 SIMPLE fsys range
fs_syslog_1,fs_syslog_2 fs_syslog_1 5
{null} 17680 Using where; Using temporary; Using
filesort
1 SIMPLE h ALL
PRIMARY,hosts_1,hosts_2 {null} {null}
{null} 96 Using where
Thanks,
Mark Ponthier
From: Ananda Kumar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 14, 2007 12:46 AM
To: Mark Ponthier
Cc: mysql@lists.mysql.com
Subject: Re: Slow query involving ORDER BY
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 <http://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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
