Hello.
Usually working with IP addresses in a numeric form is faster. Use INET_NTOA() and INET_ATON() functions to store IP addresses as unsigned ints. To work with subnetworks instead of "like 'xxx.xxx.%'" use "ip_address_in_numeric_form between inet_aton('xxx.xxx.0.0') and inet_aton('xxx.xxx.255.255')" or similar condition (check if between covers the borders of the subnet). %php% are usually slow. Force your application which inserts data to the database explicitly determine the type of the content (perhaps, you should add a field which will indicate the content. I agree, that this is a superfluity, however the speed of the query is more important). "Jay Paulson (CE CEN)" <[EMAIL PROTECTED]> wrote: >Below is a query I'm trying to create and the sql for the table I'm pulling the >information out of. The query is definitely not the best query out there >especially >since I'm still pretty new with sql. I know there has to be a better way of >getting the >information I want. A little background. I'm parsing an Apache access_log >file and >throwing it all into a DB so I can run some reports on it. What I'm wanting >to get are >certain file types that were downloaded (in this case all .html, .php, .pdf, >.doc, and >.flv files) in a certain date range but grouped by certain ip addresses. Some >groups of >ips might have 2 or 3 subnets it needs to get (ie xxx.xxx.% and yyy.yyy.%). >What needs >to be returned is the count of all the file types that have been downloaded >but grouped >by certain ips that I'm looking for. > >I hope I didn't confuse anything because I think I confused myself! ;) > >Thanks for any help! > >CREATE TABLE `apache_statslog` ( > `STATS_ID` int(11) NOT NULL auto_increment, > `ip` varchar(25) default NULL, > `accesstime` datetime default NULL, > `thepage` varchar(250) default NULL, > `thetype` varchar(25) default NULL, > `thecode` char(3) default NULL, > `thebytes` int(11) default NULL, > `theref` varchar(250) default NULL, > `browser` varchar(250) default NULL, > PRIMARY KEY (`STATS_ID`), > KEY `ip` (`ip`), > KEY `accesstime` (`accesstime`), > KEY `thepage` (`thepage`) >) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=59145 ; > > > >SELECT count(swrm.ip) as swrm_page_hits, count(mw.ip) as mw_page_hits, >count(fl.ip) as >fl_page_hits, count(so.ip) as so_page_hits > >FROM apache_statslog as swrm, apache_statslog as mw, apache_statslog as fl, >apache_statslog as so > >WHERE (swrm.accesstime >= '2006-01-01 00:00:00' AND swrm.accesstime <= >'2006-01-04 >23:59:59') > AND (swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip > LIKE 'xxx.xxx.%') >#this is a group that needs to return a count > AND (mw.ip LIKE 'xxx.xxx.%' OR mw.ip LIKE 'xxx.xxx.%') #this is a > group that needs to >return a count > AND (fl.ip LIKE 'xxx.xxx.%' OR fl.ip LIKE 'xxx.xxx.%') #this is a > group that needs to >return a count > AND (so.ip LIKE 'xxx.xxx.%' OR so.ip LIKE 'xxx.xxx.%') #this is a > group that needs to >return a count > > AND (swrm.thepage LIKE '%.html%' OR swrm.thepage LIKE '%.php%' OR > swrm.thepage LIKE >'%.doc%' OR swrm.thepage LIKE '%.pdf%' OR swrm.thepage LIKE '%.flv%') > >ORDER BY swrm.accesstime ASC -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]