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