CREATE TABLE `UAMS_User` (
`eid` bigint(20) unsigned NOT NULL,
`sid` char(32) DEFAULT NULL,
`pid` bigint(20) unsigned NOT NULL,
`clearance` tinyint(1) unsigned DEFAULT '0',
`admin` tinyint(1) unsigned DEFAULT '0',
`status` tinyint(1) unsigned DEFAULT '1',
`viewed` tinyint(1) unsigned DEFAULT '0',
`verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
`disabled` tinyint(1) unsigned DEFAULT '0',
`authforms` tinyint(1) unsigned DEFAULT '0',
`w9` tinyint(1) unsigned NOT NULL DEFAULT '0',
`attacks` tinyint(1) unsigned DEFAULT '0',
`logins` tinyint(1) unsigned DEFAULT '0',
`ctime` datetime DEFAULT NULL,
`ltime` datetime DEFAULT NULL,
`mtime` datetime DEFAULT NULL,
`rcwid` char(12) DEFAULT '0',
`rcwpin` char(4) NOT NULL DEFAULT '0',
`email` varchar(66) DEFAULT NULL,
`pwd` char(32) DEFAULT NULL,
`question` varchar(32) NOT NULL DEFAULT 'What is 2+2',
`answer` varchar(24) NOT NULL DEFAULT '4',
`browser` varchar(13) DEFAULT NULL,
`os` varchar(13) DEFAULT NULL,
`distro` varchar(13) DEFAULT NULL,
`cell` bigint(20) unsigned DEFAULT '0',
`carrier` char(3) DEFAULT 'ZZZ',
`devicetype` char(1) DEFAULT NULL,
`avatar` char(24) DEFAULT 'no-photo.png',
`name` varchar(20) DEFAULT NULL,
`phone` bigint(20) unsigned DEFAULT '0',
`address` tinytext,
`city` varchar(12) DEFAULT NULL,
`region` varchar(16) DEFAULT NULL,
`postalcode` varchar(10) DEFAULT NULL,
`country` char(2) DEFAULT NULL,
`timezone` tinyint(1) DEFAULT NULL,
`ip` int(10) unsigned DEFAULT NULL,
`parentdomain` varchar(64) DEFAULT NULL,
`mydomain` varchar(64) DEFAULT NULL,
`facebook` varchar(96) NOT NULL,
`twitter` varchar(96) NOT NULL,
`youtube` varchar(96) NOT NULL,
`myspace` varchar(96) NOT NULL,
`flickr` varchar(96) NOT NULL,
PRIMARY KEY (`eid`),
KEY `UAMS_User_fk_pid` (`pid`),
KEY `UAMS_User_idx_email` (`email`),
CONSTRAINT `UAMS_User_fk_pid` FOREIGN KEY (`pid`) REFERENCES `UAMS_User` (`eid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DROP FUNCTION IF EXISTS `User_Genealogy_First_InTier`$$
CREATE FUNCTION `User_Genealogy_First_InTier`(sp_user bigint(20) unsigned,sp_tier tinyint unsigned) RETURNS bigint(20) unsigned
BEGIN
set @c = sp_user;
drop TEMPORARY TABLE if exists T1;
drop TEMPORARY TABLE if exists T2;
CREATE TEMPORARY TABLE if not exists T1 (eid bigint unsigned) TYPE=MyISAM;
CREATE TEMPORARY TABLE if not exists T2 (eid bigint unsigned,ctime datetime) TYPE=MyISAM;
delete from T1;
insert into T1 values (@c);
delete from T2;
insert into T2 select eid,ctime from UAMS_User where pid in (select eid from T1) and eid>666 and status>9;
set @i = 1;
while (sp_tier > (@i)) do
set @i = @i+1;
delete from T1;
insert into T1 select eid from T2;
delete from T2;
insert into T2 select eid,ctime from UAMS_User where pid in (select eid from T1) and eid>666 and status>9;
end while;
select eid into @c from
(
select eid,User_Genealogy_Role_Name(eid) as role,User_Transaction_Points_PV(eid) as points,ctime from T2
)
as tmp_tbl where not role='distributor' and not (role='customer' and points < 1000) order by ctime limit 1;
return @c;
END$$
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free
UAMS_User InnoDB 10 Compact 1037 347 360448 0 131072 10485760
sp_datetime is an incoming parameter for a sp, and ltime is a field in the users table. I don't index the times, but perhaps i could index them to speed up queries; but from what i understand, indexing alone does not reduce locking: only primary key indexes reduce which records are locked --right?
Thanks!
premium domain names that i'm selling
Edited 1 time(s). Last edit at 06/06/2010 10:40AM by Vector Thorn.