Posted by James Nunnerley on 03/07/05 18:34

Further to my email last week, I've now indexed all the fields that get searched
on (i.e. sql statement is similar to "select x, y from table where x='blah'" - x
is the indexed field).

Is that the correct field to index?

My table structure is now as follows:
CREATE TABLE `syslog` (
`ID` int(100) NOT NULL auto_increment, `unixtime` int(20) NOT NULL default
'0', `date` date default NULL, `time` time default NULL, `device_id`
varchar(255) default NULL, `log_id` varchar(255) default NULL, `type`
varchar(255) default NULL, `subtype` varchar(255) default NULL, `pri`
varchar(255) default NULL, `SN` varchar(255) default NULL, `duration`
varchar(255) default NULL, `policyid` varchar(255) default NULL, `proto`
varchar(255) default NULL, `service` varchar(255) default NULL, `status`
varchar(255) default NULL, `src` varchar(255) default NULL, `srcname`
varchar(255) default NULL, `dst` varchar(255) default NULL, `dstname`
varchar(255) default NULL, `src_int` varchar(255) default NULL, `dst_int`
varchar(255) default NULL, `sent` varchar(255) default NULL, `rcvd`
varchar(255) default NULL, `sent_pkt` varchar(255) default NULL, `rcvd_pkt`
varchar(255) default NULL, `src_port` varchar(255) default NULL, `dst_port`
varchar(255) default NULL, `vpn` varchar(255) default NULL, `tran_ip`
varchar(255) default NULL, `tran_port` varchar(255) default NULL, `user`
varchar(255) NOT NULL default '', `ui` varchar(255) NOT NULL default '',
`action` varchar(255) NOT NULL default '', `reason` varchar(255) NOT NULL
default '', `msg` varchar(255) NOT NULL default '', `vd` varchar(255) NOT
NULL default '', `hostname` varchar(255) NOT NULL default '', `module`
varchar(255) NOT NULL default '', `submodule` varchar(255) NOT NULL default
'', `virdb` varchar(255) NOT NULL default '', `idsdb` varchar(255) NOT NULL
default '', `libav` varchar(255) NOT NULL default '', `aven` varchar(255) NOT
NULL default '', `imap` varchar(255) NOT NULL default '', `smtp` varchar(255)
NOT NULL default '', `pop3` varchar(255) NOT NULL default '', `http`
varchar(255) NOT NULL default '', `ftp` varchar(255) NOT NULL default '',
`fcni` varchar(255) NOT NULL default '', `fdni` varchar(255) NOT NULL default
'', `idsmn` varchar(255) NOT NULL default '', `idssn` varchar(255) NOT NULL
default '', `rbldb` varchar(255) NOT NULL default '', `seq` varchar(255) NOT
NULL default '', `old_sintf` varchar(255) NOT NULL default '', `old_dintf`
varchar(255) NOT NULL default '', `old_saddr` varchar(255) NOT NULL default
'', `old_daddr` varchar(255) NOT NULL default '', `old_schd` varchar(255) NOT
NULL default '', `old_svr` varchar(255) NOT NULL default '', `old_act`
varchar(255) NOT NULL default '', `old_nat` varchar(255) NOT NULL default '',
`old_log` varchar(255) NOT NULL default '', `new_sintf` varchar(255) NOT NULL
default '', `new_dintf` varchar(255) NOT NULL default '', `new_saddr`
varchar(255) NOT NULL default '', `new_daddr` varchar(255) NOT NULL default
'', `new_schd` varchar(255) NOT NULL default '', `new_svr` varchar(255) NOT
NULL default '', `new_act` varchar(255) NOT NULL default '', `new_nat`
varchar(255) NOT NULL default '', `new_log` varchar(255) NOT NULL default '',
`sport` varchar(255) NOT NULL default '', `dport` varchar(255) NOT NULL default
'', `cat` varchar(255) NOT NULL default '', `cat_desc` varchar(255) NOT NULL
default '', `url` varchar(255) NOT NULL default '', `from` varchar(255) NOT
NULL default '', `to` varchar(255) NOT NULL default '', `file` varchar(255)
NOT NULL default '', `virus` varchar(255) NOT NULL default '', `intf`
varchar(255) NOT NULL default '', `attack_id` varchar(255) NOT NULL default
'', `dir_disp` varchar(255) NOT NULL default '', `tran_disp` varchar(255) NOT
NULL default '', `name` varchar(255) NOT NULL default '', `local` varchar(255)
NOT NULL default '', `remote` varchar(255) NOT NULL default '', `assigned`
varchar(255) NOT NULL default '', `stat` varchar(255) NOT NULL default '',
`loc_ip` varchar(255) NOT NULL default '', `loc_port` varchar(255) NOT NULL
default '', `rem_ip` varchar(255) NOT NULL default '', `rem_port`
varchar(255) NOT NULL default '', `out_if` varchar(255) NOT NULL default '',
`vpn_tunnel` varchar(255) NOT NULL default '', `init` varchar(255) NOT NULL
default '', `mode` varchar(255) NOT NULL default '', `stage` varchar(255) NOT
NULL default '', `dir` varchar(255) NOT NULL default '', `spi` varchar(255)
NOT NULL default '', `old_status` varchar(255) NOT NULL default '',
`new_status` varchar(255) NOT NULL default '', `passwd` varchar(255) NOT NULL
default '', `sintf` varchar(255) NOT NULL default '', `dintf` varchar(255)
NOT NULL default '', `saddr` varchar(255) NOT NULL default '', `daddr`
varchar(255) NOT NULL default '', `schd` varchar(255) NOT NULL default '',
`svr` varchar(255) NOT NULL default '', `act` varchar(255) NOT NULL default
'', `nat` varchar(255) NOT NULL default '', `log` varchar(255) NOT NULL
default '', `in_spi` varchar(255) NOT NULL default '',
`out_spi` varchar(255) NOT NULL default '', `error` varchar(255) NOT NULL
default '', `seqno` varchar(255) NOT NULL default '', `field` varchar(255)
NOT NULL default '', `old` varchar(255) NOT NULL default '', `new`
varchar(255) NOT NULL default '', `gateway` varchar(255) NOT NULL default '',
`interface` varchar(255) NOT NULL default '', `old_device` varchar(255) NOT
NULL default '', `old_distance` varchar(255) NOT NULL default '', `old_dst`
varchar(255) NOT NULL default '', `new_device` varchar(255) NOT NULL default
'', `new_distance` varchar(255) NOT NULL default '', `new_dst` varchar(255)
NOT NULL default '', `device` varchar(255) NOT NULL default '', `distance`
varchar(255) NOT NULL default '', `icmp_id` varchar(255) NOT NULL default '',
`icmp_type` varchar(255) NOT NULL default '', `icmp_code` varchar(255) NOT NULL
default '',
PRIMARY KEY (`ID`), KEY `unixtime` (`unixtime`), KEY `subtype` (`subtype`),
KEY `type` (`type`), KEY `src` (`src`), KEY `dst` (`dst`), KEY `dst_port`
(`dst_port`), KEY `src_port` (`src_port`), KEY `srcname` (`srcname`))
TYPE=MyISAM AUTO_INCREMENT=1481721 ;

Sorry for it being so long, but that's the table!
Currently there are 1.5 million (1,500,00) records, and searching the table for
the last 10 records is taking up to 40 seconds.
e.g. select unixtime, type, subtype, src, dst, msg, pri from syslog where
type='ips' ORDER BY unixtime DESC LIMIT 10
Does anyone have any suggestions for improving the search?

Cheers
James




--------------------------------------------------------------------------------

From: Gareth Williams [mailto:gareth@venditor.com]
Sent: Fri 04/03/2005 13:17
To: James Nunnerley
Cc: php-general@lists.php.net
Subject: Re: [PHP] Improving a MySQL Search




Create some indexes. I spend most of my day messing around with a
table of 2000000 records, and indexes increased the speed from 40
seconds to 55 minutes for a query, down to the longest being less than
5 seconds.


On 28 Feb 2005, at 16:42, James Nunnerley wrote:

> I'm creating a serious of pages that show various aspects from a Syslog
> output.
>
> The main information is currently stored in one MySQL table - which
> after
> having been run for about 2 and a bit months has got nearly 1000000
> records.
> With this amount of information, it's taking a large time to carryout
> even
> the simplest query.
>
>
>
> Someone has suggested I look at setting up an archive, for slower
> searches,
> and a more recent table for quicker searching, but I'm reluctant to do
> this,
> as it would take quite a bit of time to copy all the information
> across as
> it stands, and then also to run a cron which transfers between the
> live and
> archive.
>
>
>
> Can anyone suggest some quick easy methods for speeding up the search?
>
>
>
> Cheers
>
> James
>

[Back to original message]