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" <mary.stickney@stripped>
To: "Mary Stickney" <mary.stickney@stripped>; "Jocelyn Fournier"
<joc@stripped>; "Mark Matthews" <mmatthew@stripped>;
<mysql@stripped>
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:mary.stickney@stripped]
> Sent: Friday, August 16, 2002 11:17 AM
> To: Jocelyn Fournier; Mark Matthews; mysql@stripped
> 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:joc@stripped]
> Sent: Friday, August 16, 2002 11:01 AM
> To: Mary Stickney; Mark Matthews; mysql@stripped
> Subject: Re: MySQL vs. Oracle (not speed)
>
>
> Could you please send also tempsap ?
>
> Thanks and regards,
> Jocelyn
>
> ----- Original Message -----
> From: "Mary Stickney" <mary.stickney@stripped>
> To: "Mark Matthews" <mmatthew@stripped>; <mysql@stripped>
> 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:mmatthew@stripped]
> > Sent: Friday, August 16, 2002 9:15 AM
> > To: Mary Stickney; mysql@stripped
> > 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 <mysql-thread117321@stripped>
> > To unsubscribe, e-mail
> > <mysql-unsubscribe-mary.stickney=tagtmi.com@stripped>
> > 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 <mysql-thread117340@stripped>
> > To unsubscribe, e-mail
> <mysql-unsubscribe-joc=presence-pc.com@stripped>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
> >
> >
>
>
>
>
