The reason for the limit 2500 is that I want each iteration of the script to work very quickly, independent of the size of the database. When I first run this script to calculate 5000 address against 120 t-stops [t=Trolley], I want it to take a couple iterations.
The script is on a crontab running every 5 minutes, most times there will be nothing to do, but if an address is added, then obviously, it would calculate it.
Code: Select all
SELECT address.id AS aid, t_station.id AS tid FROM address, t_station WHERE address.status = 'Active' AND
t_station.status = 'Active' AND ( SELECT COUNT(*) FROM t_relation WHERE t_relation.status = 'Active' AND address_id = address.id AND t_station_id = t_station.id AND MD5(CONCAT_WS('-',address.latitude,address.longitude)) = md5_address AND MD5(CONCAT_WS('-',t_station.latitude,t_station.longitude)) = md5_station ) = 0 LIMIT 2500
Code: Select all
CREATE TABLE `t_relation` (
`t_station_id` int(2) unsigned default NULL,
`md5_station` varchar(32) default NULL,
`address_id` int(36) default NULL,
`md5_address` varchar(32) default NULL,
`miles` double default NULL,
`timestamp` datetime default NULL,
`status` enum('Active','Disabled') NOT NULL,
`id` int(16) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Code: Select all
CREATE TABLE `address` (
`street_number` varchar(8) default NULL,
`street_name` varchar(64) default NULL,
`street_type` varchar(8) default NULL,
`city` varchar(32) default NULL,
`state` char(2) default NULL,
`zip` decimal(5,0) unsigned zerofill default NULL,
`longitude` double default NULL,
`latitude` double default NULL,
`exact` enum('Yes','No') NOT NULL default 'Yes',
`timestamp` datetime default NULL,
`status` enum('Active','Disabled') NOT NULL,
`id` int(36) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`),
UNIQUE KEY `street_number` (`street_number`,`street_name`,`street_type`,`city`,`state`,`zip`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Code: Select all
CREATE TABLE `t_station` (
`title` varchar(32) default NULL,
`line` varchar(8) default NULL,
`longitude` double default NULL,
`latitude` double default NULL,
`timestamp` datetime default NULL,
`status` enum('Active','Disabled') NOT NULL,
`id` int(2) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`),
UNIQUE KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;