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 '-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
