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)" <Jay.Paulson@stripped> 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
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
