Hi, We log all sessions on our webserver to a table, and then periodically run a batch process to analyze the visitors ip addresses to determine the city/region/country of origin.
we use MySQL 4.1.1-alpha-standard-log on Redhat AS, installed from the mysql RPM. The query below takes about 1 second to run, which is fine on its own, but it has to be run for 35+ locations. I *think* I've created all possible indexes to speed this up, but if anyone could help it would be much appreciated. cheers, chris QUERY -------------------------------------------------- /* The concat is required to stop Coldfusion seeing this column as binary data */ SELECT CONCAT(country.name, '') AS country, ipaddress.region, ipaddress.city, /* Calculate distance in miles of visitor to city they are visiting */ ROUND(3957 * 2 * ATAN2(SQRT(POW((SIN(0.0174*(ipaddress.latitude-56.847)/2)),2) + COS(0.0174*56.847) * COS(0.0174*ipaddress.latitude) * POW((SIN(0.0174*(ipaddress.longitude--2.477)/2)),2)),SQRT(1 -(POW((SIN(0.0174*(ipaddress.latitude-56.847)/2)),2) + COS(0.0174*56.847) * COS(0.0174*56.847) * POW((SIN(0.0174*(ipaddress.longitude--2.477)/2)),2))))) AS distance, COUNT(ipaddress.ipnumber) AS visits FROM (country INNER JOIN (ipaddress INNER JOIN sessionLog ON ipaddress.ipnumber = sessionLog.ipnumber) ON country.code = ipaddress.country) INNER JOIN userAgent ON sessionLog.browser_id = userAgent.id WHERE sessionLog.initialDirectory = 1 AND userAgent.searchEngine = 0 AND sessionLog.sessionStartTime BETWEEN {ts '2004-09-01 00:00:00'} AND {ts '2004-09-30 23:59:59'} GROUP BY country, region, city ORDER BY visits DESC EXPLAIN OUTPUT +----+-------------+------------+--------+---------------------------------- --------------------------------------+------------------------+---------+-- --------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------------------------- --------------------------------------+------------------------+---------+-- --------------------------+------+---------------------------------+ | 1 | SIMPLE | country | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 192 | Using temporary; Using filesort | | 1 | SIMPLE | userAgent | eq_ref | PRIMARY | PRIMARY | 4 | ukcd.sessionLog.browser_id | 1 | Using where | | 1 | SIMPLE | sessionLog | ref | idx_sessionloginitdirid,idx_sessionlogstarttime,idx_sessionlogipnumber | idx_sessionlogipnumber | 4 | ukcd.ipaddress.ipnumber | 30 | Using where | | 1 | SIMPLE | ipaddress | ref | PRIMARY,idx_country | idx_country | 2 | ukcd.country.code | 52 | | +----+-------------+------------+--------+---------------------------------- --------------------------------------+------------------------+---------+-- --------------------------+------+---------------------------------+ TABLE DEFS ------------------------------------------------------- # contains 192 rows CREATE TABLE `country` ( `id` int(11) NOT NULL default '0', `code` char(2) default NULL, `name` char(100) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM DEFAULT CHARSET=latin1; # 10,000 rows CREATE TABLE `ipaddress` ( `ipnumber` int(10) unsigned NOT NULL default '0', `country` char(2) NOT NULL default '', `region` varchar(25) NOT NULL default '', `city` varchar(25) NOT NULL default '', `postalCode` varchar(8) NOT NULL default '', `latitude` double NOT NULL default '0', `longitude` double NOT NULL default '0', `dmacode` varchar(100) NOT NULL default '', `areacode` varchar(100) NOT NULL default '', `isp` varchar(100) NOT NULL default '', `errcode` varchar(100) NOT NULL default '', `organisation` varchar(100) NOT NULL default '', PRIMARY KEY (`ipnumber`), KEY `idx_country` (`country`), KEY `idx_city` (`city`) ) TYPE=MyISAM DEFAULT CHARSET=latin1; # 250,000 rows CREATE TABLE `sessionLog` ( `session_id` varchar(70) NOT NULL default '', `browser_id` int(11) NOT NULL default '0', `sessionStartTime` datetime NOT NULL default '0000-00-00 00:00:00', `initialDirectory` int(11) NOT NULL default '0', `ipnumber` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`session_id`), KEY `idx_sessionloginitdirid` (`initialDirectory`), KEY `idx_sessionlogstarttime` (`sessionStartTime`), KEY `idx_sessionlogipnumber` (`ipnumber`) ) TYPE=MyISAM DEFAULT CHARSET=latin1; # 2,000 rows CREATE TABLE `userAgent` ( `id` int(11) NOT NULL auto_increment, `uaString` varchar(255) NOT NULL default '', `searchEngine` tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `idx_ua` (`uaString`) ) TYPE=MyISAM DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]