List:General Discussion« Previous MessageNext Message »
From:Jocelyn Fournier Date:August 16 2002 4:40pm
Subject:Re: MySQL vs. Oracle (not speed)
View as plain text  
You can if you have a MySQL version > 3.23.2, and are using the MyISAM
handler.
(http://www.mysql.com/doc/en/CREATE_INDEX.html)

What version of MySQL do you use ??


----- Original Message -----
From: "Mary Stickney" <mary.stickney@stripped>
To: "Jocelyn Fournier" <joc@stripped>; "Mark Matthews"
<mmatthew@stripped>; <mysql@stripped>
Sent: Friday, August 16, 2002 6:38 PM
Subject: RE: MySQL vs. Oracle (not speed)


>
> looks to me like I can only make indexes on fields that are not null...
> this one is not , not null
>
>
> -----Original Message-----
> From: Jocelyn Fournier [mailto:joc@stripped]
> Sent: Friday, August 16, 2002 11:34 AM
> To: Mary Stickney; Mark Matthews; mysql@stripped
> Subject: Re: MySQL vs. Oracle (not speed)
>
>
> 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
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
>
>
>

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