RE: [PHP] Improving a MySQL Search

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 >