Re: Question about the Query Cache and server settings
Ok, you asked for it. You know I could NOT believe this first table when I looked at it. I found it astounding.
Here goes the show create table w3t_Users:
Create Table: CREATE TABLE `w3t_Users` (
`U_LoginName` varchar(64) NOT NULL default '',
`U_Username` varchar(30) NOT NULL default '',
`U_Password` varchar(150) NOT NULL default '',
`U_Email` varchar(50) default NULL,
`U_Fakeemail` varchar(50) default NULL,
`U_First_Name` varchar(100) NOT NULL default '',
`U_Last_Name` varchar(100) NOT NULL default '',
`U_Name` varchar(100) default NULL,
`U_Company` varchar(150) NOT NULL default '',
`U_Totalposts` int(9) unsigned default '0',
`U_Laston` int(11) unsigned default NULL,
`U_Signature` text,
`U_Homepage` varchar(150) default NULL,
`U_Occupation` varchar(150) default NULL,
`U_Hobbies` varchar(200) default NULL,
`U_Location` varchar(200) default NULL,
`U_Address` varchar(150) NOT NULL default '',
`U_Address2` varchar(150) NOT NULL default '',
`U_City` varchar(75) NOT NULL default '',
`U_State` varchar(75) NOT NULL default '',
`U_Zip` varchar(25) NOT NULL default '',
`U_Country` varchar(75) NOT NULL default '',
`U_Phone` varchar(30) NOT NULL default '',
`U_Mobile` varchar(50) NOT NULL default '',
`U_Bio` text,
`U_Status` varchar(15) NOT NULL default 'User',
`U_Sort` int(4) unsigned default '5',
`U_Display` varchar(10) NOT NULL default 'flat',
`U_View` varchar(10) NOT NULL default 'collapsed',
`U_PostsPer` int(11) unsigned default '10',
`U_Number` int(9) unsigned NOT NULL auto_increment,
`U_EReplies` char(3) NOT NULL default 'Off',
`U_Notify` char(3) NOT NULL default '',
`U_TextCols` char(3) default '60',
`U_TextRows` char(3) default '5',
`U_Extra1` varchar(200) default NULL,
`U_Extra2` varchar(200) default NULL,
`U_Extra3` varchar(200) default NULL,
`U_Extra4` varchar(200) default NULL,
`U_Extra5` varchar(200) default NULL,
`U_Post_Format` varchar(5) NOT NULL default 'side',
`U_Registered` int(11) unsigned default NULL,
`U_Preview` varchar(5) default 'on',
`U_Picture` varchar(150) default NULL,
`U_PictureView` char(3) default NULL,
`U_Visible` char(3) default 'yes',
`U_PicturePosts` char(3) default NULL,
`U_AcceptPriv` char(3) default 'yes',
`U_RegEmail` varchar(50) default NULL,
`U_RegIP` varchar(15) default NULL,
`U_Groups` varchar(250) default '-3-',
`U_Language` varchar(20) default 'english',
`U_Title` varchar(100) default 'stranger',
`U_FlatPosts` char(2) default NULL,
`U_TempPass` varchar(150) default NULL,
`U_Color` varchar(15) default 'FF0000',
`U_TempRead` text,
`U_StyleSheet` varchar(50) default 'usedefault',
`U_TimeOffset` varchar(10) default '0',
`U_Privates` int(4) unsigned default '0',
`U_FrontPage` varchar(20) default 'wwwthreads',
`U_ActiveThread` int(4) NOT NULL default '0',
`U_StartPage` char(2) NOT NULL default '',
`U_Favorites` varchar(250) NOT NULL default '',
`U_ShowSigs` char(3) default NULL,
`U_OnlineFormat` char(3) default NULL,
`U_Rating` varchar(5) NOT NULL default '',
`U_Rates` int(4) NOT NULL default '0',
`U_RealRating` int(1) default '0',
`U_PicWidth` int(4) default '0',
`U_PicHeight` int(4) default '0',
`U_SessionId` varchar(64) NOT NULL default '',
`U_Approved` varchar(8) NOT NULL default '',
`U_Palprofile` int(11) default '0',
`U_AdminEmails` char(3) default NULL,
`U_EmailFormat` varchar(10) NOT NULL default '',
`U_WhichForums` char(3) NOT NULL default '',
`U_Categories` varchar(255) NOT NULL default '',
`U_Banned` int(1) default '0',
`U_CoppaUser` int(1) default '0',
`U_Birthday` varchar(10) NOT NULL default '0',
`U_ShowBday` int(1) default '0',
`U_TimeFormat` varchar(6) default NULL,
`U_Ignored` text,
`U_OC_Userid` varchar(64) default NULL,
`U_ProfileHash` varchar(32) default NULL,
`U_Elist` varchar(5) NOT NULL default 'yes',
`U_Webmail` char(3) NOT NULL default 'no',
`U_Member_status` varchar(12) NOT NULL default 'free',
`U_Sku` varchar(20) NOT NULL default '',
`U_Paid` char(3) NOT NULL default 'no',
`U_Renew` char(3) NOT NULL default 'no',
`U_Old_Skus` varchar(255) NOT NULL default '',
`U_Gender` char(1) NOT NULL default '',
`U_DisplayName` varchar(64) NOT NULL default '',
`signup_source` varchar(30) NOT NULL default '',
`purchase_source` varchar(30) NOT NULL default '',
`credits` int(11) NOT NULL default '0',
`active` varchar(10) NOT NULL default 'yes',
`age` varchar(20) NOT NULL default '0',
`income` varchar(15) NOT NULL default '',
`priv` varchar(10) NOT NULL default 'user',
`ff_id` varchar(150) NOT NULL default '',
`qty` int(4) NOT NULL default '0',
`couponCode` varchar(50) NOT NULL default '',
`ff_coupon` varchar(50) default NULL,
`ff_coupon_used` enum('no','yes') NOT NULL default 'no',
`ff_coupon_exp` datetime NOT NULL default '0000-00-00 00:00:00',
`specialMember` char(3) NOT NULL default 'no',
`exp_date` int(20) NOT NULL default '0',
`virgin_login` char(3) NOT NULL default 'no',
`ip_check` varchar(20) NOT NULL default '',
`order_num` int(150) NOT NULL default '0',
`last_modified` timestamp(14) NOT NULL,
`created` int(20) NOT NULL default '0',
`U_Last_Pass` int(35) NOT NULL default '0',
`U_LastPostTime` int(11) unsigned default NULL,
`U_LastPostIP` varchar(15) default NULL,
`U_LastSearch` int(11) default NULL,
`U_SearchSession` varchar(32) default NULL,
`U_FloodControl` mediumint(8) default NULL,
`U_LastPost` int(11) unsigned default NULL,
`code` varchar(6) default NULL,
`ticketmaster` varchar(12) default NULL,
`tm_code_used` char(3) NOT NULL default 'no',
`tm_code_used_on` datetime NOT NULL default '0000-00-00 00:00:00',
`subscribed_on` datetime NOT NULL default '0000-00-00 00:00:00',
`Refund_Request` char(3) NOT NULL default 'no',
`Refund_Request_Date` datetime NOT NULL default '0000-00-00 00:00:00',
`refund_issued` char(3) NOT NULL default 'no',
`no_renew` char(3) NOT NULL default 'no',
PRIMARY KEY (`U_Number`),
UNIQUE KEY `U_Username` (`U_Username`),
KEY `indx1` (`U_Username`,`U_Password`),
KEY `indx2` (`U_Status`),
KEY `U_LoginName` (`U_LoginName`),
KEY `U_Email` (`U_Email`),
KEY `U_DisplayName` (`U_DisplayName`),
KEY `U_Birthday` (`U_Birthday`),
KEY `ticketmaster` (`ticketmaster`),
KEY `specialMember` (`specialMember`)
) TYPE=MyISAM
I know that indx1 is redundant and should be dropped. I also created a covering index that has not made it to the production server.
I'll put w3t_Posts on another reply ...
erin