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
