List:General Discussion« Previous MessageNext Message »
From:Mary Stickney Date:August 16 2002 4:29pm
Subject:RE: MySQL vs. Oracle (not speed)
View as plain text  
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
>
>
>
>

Thread
MySQL vs. Oracle (not speed)Elizabeth Bogner16 Aug
  • Re: MySQL vs. Oracle (not speed)Matthew Walker16 Aug
  • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
    • RE: MySQL vs. Oracle (not speed)Kent Vilhelmsen16 Aug
    • RE: MySQL vs. Oracle (not speed)Francisco16 Aug
      • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
        • Re: MySQL vs. Oracle (not speed)Mark Matthews16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • Re: MySQL vs. Oracle (not speed)Mark Matthews16 Aug
              • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
              • Re: MySQL vs. Oracle (not speed)Serge Paquin16 Aug
                • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                  • Re: MySQL vs. Oracle (not speed)Mark Matthews16 Aug
                • RE: MySQL vs. Oracle (not speed)Aron Pilhofer17 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
              • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                  • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
                    • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                      • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
                      • Re: MySQL vs. Oracle (not speed)Van16 Aug
                        • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                      • Re: MySQL vs. Oracle (not speed)Dan Nelson16 Aug
                        • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                      • RE: MySQL vs. Oracle (not speed)Aron Pilhofer17 Aug
        • RE: MySQL vs. Oracle (not speed)Francisco16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
              • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                • Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
                  • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
                    • Re: improving slow query (was: MySQL vs. Oracle (not speed))Benjamin Pflugmann16 Aug
                • RE: MySQL vs. Oracle (not speed)Aron Pilhofer17 Aug
            • RE: MySQL vs. Oracle (not speed) - not part of the rant, but real informationAdam Nelson16 Aug
              • Re: MySQL vs. Oracle (not speed) - not part of the rant, but real informationDavid Lloyd17 Aug
        • Re: MySQL vs. Oracle (not speed)Tom Gao16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
        • RE: MySQL vs. Oracle (not speed)Aron Pilhofer16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • RE: MySQL vs. Oracle (not speed)Aron Pilhofer16 Aug
              • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
            • Re: MySQL vs. Oracle (not speed)Mark Matthews16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
        • RE: MySQL vs. Oracle (not speed)Aron Pilhofer16 Aug
        • RE: MySQL vs. Oracle (not speed)Aron Pilhofer16 Aug
    • Re: MySQL vs. Oracle (not speed)Gelu Gogancea16 Aug
      • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
        • Re: MySQL vs. Oracle (not speed)Gelu Gogancea16 Aug
          • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
  • Re: MySQL vs. Oracle (not speed)Anderson Pereira Ataides16 Aug
RE: MySQL vs. Oracle (not speed)Art Fore16 Aug
RE: MySQL vs. Oracle (not speed)John Griffin16 Aug
  • Re: MySQL vs. Oracle (not speed)Elizabeth Bogner17 Aug
Re: MySQL vs. Oracle (not speed)Heikki Tuuri16 Aug
RE: MySQL vs. Oracle (not speed)Alec.Cawley16 Aug
RE: MySQL vs. Oracle (not speed)John Griffin16 Aug
  • RE: MySQL vs. Oracle (not speed)Mary Stickney16 Aug
Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
Re: MySQL vs. Oracle (not speed)Heikki Tuuri16 Aug
RE: MySQL vs. Oracle (not speed)Kenneth Hylton16 Aug
Re: MySQL vs. Oracle (not speed)Jocelyn Fournier16 Aug
  • RE: MySQL vs. Oracle (not speed)Mary Stickney17 Aug
RE: MySQL vs. Oracle (not speed)Steve Orr16 Aug
RE: MySQL vs. Oracle (not speed)Heikki Tuuri16 Aug