Hi, I see you do an order by on AdminHierarchy.WritingAgentSlot but this column is not indexed. Please tell me if an index on this column improve the speed of the query a bit.
Regards, Jocelyn ----- Original Message ----- From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Mary Stickney" <[EMAIL PROTECTED]>; "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 6:29 PM Subject: RE: MySQL vs. Oracle (not speed) > > 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
