I also noticed the field where you make the join are not exactly of the same type :
e.g : AdminHierarchy.CoverageID=AdminCoverage.CoverageID in adminHierarchy : CoverageID varchar(15) NOT NULL in admincoverage : CoverageID varchar(10) NOT NULL AdminCoverage.ProductID=AdminProduct.ProductID in adminCoverage : ProductId varchar(11) NOT NULL , in adminProduct : ProductID varchar(15) NOT NULL , AdminProducer.taxid = tempsap.taxid in adminProducer : TaxID varchar(9) NOT NULL , in tempsap : TaxID varchar(10) NOT NULL , If you want to maximize the performance you should use the same field type. ----- Original Message ----- From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 7:51 PM Subject: RE: MySQL vs. Oracle (not speed) > > I hope this makes it faster , the program just got done , it ran from 7:00 > am yesterday until noon today. > and guess what , one of my report columns is wrong , I made a boo boo in the > persistency calculation.... > > at least it friday... > > > -----Original Message----- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 12:25 PM > To: Mary Stickney > Subject: Re: MySQL vs. Oracle (not speed) > > > Ok, so it's already a MyISAM table. You should be able to add index on > WritingAgentSlot with > > ALTER TABLE adminhierarchy ADD INDEX (WritingAgentSlot); > > ----- Original Message ----- > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 7:22 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > ;Table;Create Table > > ;Table;Create Table > > ;AdminHierarchy;CREATE TABLE `adminhierarchy` ( > > `Source` char(2) NOT NULL default '', > > `WritingAgentID` varchar(15) NOT NULL default '', > > `CoverageID` varchar(15) NOT NULL default '', > > `AgentLevelID` varchar(15) NOT NULL default '', > > `ProducerID` varchar(15) NOT NULL default '', > > `ProducerTypeID` varchar(5) NOT NULL default '', > > `PercentOfCase` double default NULL, > > `RegionCode` varchar(5) default NULL, > > `CoverageIdSbc` varchar(10) NOT NULL default '', > > `WritingAgentSlot` int(11) default NULL, > > `RadDistributionCode` varchar(5) default NULL, > > KEY `WritingAgentIDIndex` (`WritingAgentID`), > > KEY `AgentLevelIDIndex` (`AgentLevelID`), > > KEY `CoverageIDIndex` (`CoverageID`), > > KEY `ProducerIDIndex` (`ProducerID`), > > KEY `ProducerTypeIDIndex` (`ProducerTypeID`), > > KEY `CoverageIdSbcIndex` (`CoverageIdSbc`), > > KEY `CovIdCovIdSbcIndex` (`CoverageID`,`CoverageIdSbc`) > > ) TYPE=MyISAM > > > > > > > > -----Original Message----- > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 12:16 PM > > To: Mary Stickney > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > SHOW CREATE TABLE AdminHierarchy; > > > > It must show what kind of table you are using. > > > > I don't think it change anything for the other tools. Just try to create a > > table in MyISAM format and see if it's correctly handled by Segate > Analysis, > > etc. > > If not, just go back to ISAM (ALTER TABLE AdminHierarchy type=ISAM, and so > > on). > > > > > > ----- Original Message ----- > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > Sent: Friday, August 16, 2002 7:14 PM > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > > > ok I am on a quest to find out what type we are using. and if changeing > > them > > > will affect > > > other things. like the Segate Analysis that the Clients use for adhoc > > > reporting > > > > > > -----Original Message----- > > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > > Sent: Friday, August 16, 2002 12:07 PM > > > To: Mary Stickney > > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > > > > Ok, so you have to convert them to MyISAM (you should have a great > > > improvement in speed then). > > > > > > Just use > > > > > > ALTER TABLE AdminHierarchy type=MyISAM; > > > ALTER TABLE AdminCoverage type=MyISAM; > > > ALTER TABLE AdminProducer type=MyISAM; > > > ALTER TABLE AdminProduct type=MyISAM; > > > ALTER TABLE tempsap type=MyISAM; > > > > > > Then you should be able to add the index on the WritingAgentSlot column. > > > > > > ----- Original Message ----- > > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > > Sent: Friday, August 16, 2002 7:03 PM > > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > > > > > > > no I am not.. I just started using this system 3 weeks ago. > > > > > > > > > > > > -----Original Message----- > > > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > > > Sent: Friday, August 16, 2002 12:00 PM > > > > To: Mary Stickney > > > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > > > > > > > Are you sure your tables used the MyISAM handler, and not the ISAM > > handler > > > ? > > > > ----- Original Message ----- > > > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > > > Sent: Friday, August 16, 2002 6:59 PM > > > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > > > > > > > > > > > no I cant thru the table manager anyway. > > > > > > > > > > I will try the create index statement. > > > > > > > > > > > > > > > > > > > > -----Original Message----- > > > > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > > > > Sent: Friday, August 16, 2002 11:51 AM > > > > > To: Mary Stickney > > > > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > > > > > > > > > > So you can create index on NULL fields :) > > > > > ----- Original Message ----- > > > > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > > > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > > > > Sent: Friday, August 16, 2002 6:51 PM > > > > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > > > > > > > > > > > > > > > 3.23.51 > > > > > > > > > > > > -----Original Message----- > > > > > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > > > > > Sent: Friday, August 16, 2002 11:41 AM > > > > > > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > > > > > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > > > > > > > > > > > > > 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
