Couple of things to read that may help: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
http://hackmysql.com/case3 On another note, you should really change all of those ip address columns from varchar to int with the ip encoded as 4 bytes. You will save A LOT of space in both your index and table. And you should reduce the other varchar columns to the smallest amount possible. -----Original Message----- From: Mark Ponthier [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 14, 2007 10:52 AM To: mysql@lists.mysql.com Subject: RE: Slow query involving ORDER BY 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] This communication is for informational purposes only. It is not intended as an offer or solicitation or as an official confirmation. Market prices and other information are not guaranteed as to completeness or accuracy and are subject to change without notice. Schonfeld Group reserves the right to monitor and review the content of all messages sent to or from this e-mail address. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]