incidentaly... Primary keys cant not be made on most of these tables due to the fact that the farther back in time you got the more screwed up the data is... Fields have been added and they did not have the info to fill in the blanks...
-----Original Message----- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:17 AM To: Jocelyn Fournier; Mark Matthews; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) I am getting the taxid's I need from here so as not to try ane merge the entire table.. and there are 833... CREATE TABLE tempsap ( TempSapRecNum mediumint(9) NOT NULL auto_increment, StatusCode char(3) , Company varchar(10) , FirstYear varchar(4) , SecondYear varchar(4) , ThruDate varchar(12) , WritingAgentID varchar(10) , GroupID varchar(10) , TaxID varchar(10) NOT NULL , RegionCode varchar(10) , RegionName varchar(50) , AgentName varchar(50) , NewCasePercent double , FirstYearSales decimal(10,2) , SecondYearSales decimal(10,2) , AnnualPremium decimal(10,2) , PercentOfCases double , PremiumsInforce decimal(10,2) , ThirdYear varchar(4) , ThirdYearSales decimal(10,2) , Producerid varchar(20) , DistributionCode varchar(10) , TotalPaidPremium decimal(10,2) , PRIMARY KEY (TempSapRecNum), KEY taxid (TaxID) ); -----Original Message----- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:01 AM To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Could you please send also tempsap ? Thanks and regards, Jocelyn ----- Original Message ----- From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 4:55 PM Subject: RE: MySQL vs. Oracle (not speed) > this is a read only datawarehouse database.... we refresh monthly from the > antiquated mainframe we have > > ok here are the tables.... > > Adminhierarchy 6.5 million records > CREATE TABLE adminhierarchy ( > Source char(2) NOT NULL , > WritingAgentID varchar(15) NOT NULL , > CoverageID varchar(15) NOT NULL , > AgentLevelID varchar(15) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerTypeID varchar(5) NOT NULL , > PercentOfCase double , > RegionCode varchar(5) , > CoverageIdSbc varchar(10) NOT NULL , > WritingAgentSlot int(11) , > RadDistributionCode varchar(5) , > KEY WritingAgentIDIndex (WritingAgentID), > KEY AgentLevelIDIndex (AgentLevelID), > KEY CoverageIDIndex (CoverageID), > KEY ProducerIDIndex (ProducerID), > KEY ProducerTypeIDIndex (ProducerTypeID), > KEY CoverageIdSbcIndex (CoverageIdSbc), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) > ); > > admin coverage 1.5 million > CREATE TABLE admincoverage ( > Source char(2) , > IsRider char(1) , > CoverageID varchar(10) NOT NULL , > CoverageIdSbc varchar(10) NOT NULL , > ParentCoverageID varchar(10) NOT NULL , > GroupID varchar(10) NOT NULL , > EmployeeID varchar(10) NOT NULL , > ProductId varchar(11) NOT NULL , > OriginalCertificateNumber varchar(20) , > StatusID varchar(10) NOT NULL , > ApplicationDate date , > effectivedate date NOT NULL , > PaidToDate date NOT NULL , > InitialPremiumDate date NOT NULL , > StatusDate date , > BenefitAmount double , > ModalPremium double , > AnnualPremium double , > AccidentElim smallint(6) , > AccidentElimUnitID varchar(5) , > SicknessElim smallint(6) , > SicknessElimUnitID varchar(5) , > AccidentBenefit double , > AccidentBenefitUnitID varchar(5) , > SicknessBenefit double , > SicknessBenefitUnitID varchar(5) , > Waived char(1) , > IssueAgePI smallint(6) , > IssueStatePI char(2) , > ResidentStatePI char(2) , > ResidentZipPI varchar(9) , > GenderPI char(1) , > DateOfBirthPI date , > TaxIDPI varchar(9) , > RelationToEmp varchar(5) , > BillTypeID varchar(10) NOT NULL , > BillModeID varchar(10) NOT NULL , > DateBilled date , > Reinsured varchar(5) , > InsuredLives char(2) , > BenefitFrequency char(1) , > BenefitPercent varchar(9) , > SmokerPI char(1) , > PaidUpDate date , > SegId char(2) , > RegionCode varchar(5) , > TerminationDate date , > KEY CoverageID (CoverageID), > KEY CoverageIdSbc (CoverageIdSbc), > KEY GroupId (GroupID), > KEY ProductID (ProductId), > KEY StatusID (StatusID), > KEY ParentCoverageIdIndex (ParentCoverageID), > KEY EffectiveDateIndex (effectivedate), > KEY PaidToDateIndex (PaidToDate), > KEY InitPremDateIndex (InitialPremiumDate), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc), > KEY CovIdCovIdSbcInitPremIndex (CoverageID, CoverageIdSbc, > InitialPremiumDate) > ); > > CREATE TABLE adminproducer ( > Source char(2) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerNbr varchar(15) NOT NULL , > IsAgency tinyint(4) , > ProducerName varchar(50) , > StatusID char(1) , > EffectiveDate date , > TerminationDate date , > TaxID varchar(9) NOT NULL , > Address1 varchar(50) , > Address2 varchar(50) , > City varchar(30) , > State char(2) , > Zip varchar(9) , > MailingAddress1 varchar(50) , > MailingAddress2 varchar(50) , > MailingCity varchar(30) , > MailingState char(2) , > MailingZip varchar(9) , > AdvanceBalance double , > DistributionCode varchar(5) , > KEY ProducerIDIndex (ProducerID), > KEY ProducerNbrIndex (ProducerNbr), > KEY TaxIDIndex (TaxID) > ); > > CREATE TABLE adminproduct ( > Source char(2) , > IsRider char(1) , > ProductID varchar(15) NOT NULL , > ProductNbr varchar(15) , > ProductDescription varchar(50) , > UnderwriterID varchar(15) , > LOBID varchar(15) NOT NULL , > BusinessType varchar(5) , > StatutoryLinesCode varchar(5) , > WaiverAvailable tinyint(4) , > AccidentElim mediumint(9) , > AccidentBen1 double , > AccidentBen2 double , > SickElim mediumint(9) , > SickBen1 double , > SickBen2 double , > KEY ProductIdIndex (ProductID), > KEY LobIdIndex (LOBID) > ); > > > -----Original Message----- > From: Mark Matthews [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 9:15 AM > To: Mary Stickney; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Mary Stickney wrote: > > I have been doing speed tests.... the same query ran on MYSQL took 45 > > minutes > > on MS-SQL it took 11 minutes...... > > > > yes you do get what you pay for.... > > Why not post the queries and the schemas here? My guess is you don't > have something indexed correctly, or are using a query that gets > optimized well by MS-SQL Server, but not MySQL. > > We all know that SQL is not absolutely portable, and that when you move > queries from database to database, that there is some work to > re-optimize them. > > There are some queries that just work better on databases other than > MySQL, but they are very few and far-between. > > Without any way to backup your claim, it is hard for anyone here to > believe that you have done everything possible to make a fair > comparison. Given your previous comments in this forum, it appears that > you must be trolling. > > -Mark > > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
