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" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:34 AM > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > 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" <[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
