explian returns this
;table;type;possible_keys;key;key_len;ref;rows;Extra ;table;type;possible_keys;key;key_len;ref;rows;Extra ;AdminCoverage;range;CoverageID,CoverageIdSbc,InitPremDateIndex,CovIdCovIdSb cIndex,CovIdCovIdSbcInitPremIndex;InitPremDateIndex;3;;315663;where used; Using temporary; Using filesort ;AdminHierarchy;ref;CoverageIDIndex,CoverageIdSbcIndex,CovIdCovIdSbcIndex;Co vIdCovIdSbcIndex;25;AdminCoverage.CoverageID,AdminCoverage.CoverageIdSbc;7;w here used ;AdminProducer;ref;ProducerIDIndex;ProducerIDIndex;15;AdminHierarchy.Writing AgentID;11; ;AdminProduct;ref;ProductIdIndex;ProductIdIndex;15;AdminCoverage.ProductId;1 1; ;tempsap;ALL;;;;;10019; -----Original Message----- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:21 AM To: Mary Stickney; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Hi, Which columns are indexed in your table ? What does EXPLAIN into MySQL return ? Regards, Jocelyn ----- Original Message ----- From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 4:31 PM Subject: RE: MySQL vs. Oracle (not speed) > > I am not for one or the other,,,, I just hate to wait.... > I need speed... > > we already have a MS-SQL server , so no more money needs to me spent... > > I did a 4 table join , drwing 3 years of sales data > > table one 6.5 million records --- AdminHierarchy > table two 1.5 million records ---- AdminCoverage > table three 10191 records ------- AdminProcuder > table four 19823 records ------- AdminProduct > > SELECT AdminHierarchy.WritingAgentID, AdminHierarchy.WritingAgentSlot, > AdminHierarchy.ProducerID, > tempsap.taxid, (ModalPremium * BillModeID * (PercentOfCase / 100)) AS > TotalPaidPremium, > AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc, > AdminHierarchy.RegionCode, > AdminProduct.LobId, AdminCoverage.StatusID, AdminCoverage.StatusDate, > AdminCoverage.InitialPremiumDate, AdminCoverage.PaidToDate, tempsap.GROUPID > FROM AdminHierarchy > INNER JOIN AdminCoverage ON > AdminHierarchy.CoverageID=AdminCoverage.CoverageID > AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc > LEFT JOIN AdminProducer ON > AdminProducer.ProducerID=AdminHierarchy.WritingAgentID > LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID > Left join tempsap on AdminProducer.taxid = tempsap.taxid > WHERE AdminCoverage.InitialPremiumDate >= '20000101' AND > AdminCoverage.InitialPremiumDate <= '20020430' > ORDER BY AdminHierarchy.WritingAgentSlot,AdminCoverage.CoverageId, > AdminCoverage.CoverageIdSbc, > AdminHierarchy.ProducerID > > > > -----Original Message----- > From: Francisco [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 9:21 AM > To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > Hi Mary, > > I am not specially against or pro MySQL, Microsoft SQL > Server, Oracle or any other database. Teams make their > choices based on the project needs such as budget (is > your team ready to spend thousands of dollars on > Oracle and marry that corporation forever?), > deployment (do you want your product with Oracle's > price tag attached to it?), functionality: does the > database server provide a viable solution technically > speaking?, etc, etc. So it is not an issue of good or > bad. > > But regardless of all that, what we should have, at > least, is a great respect for people that has been > working so hard to provide an affordable and viable > alternative to the database server giants. I don't > think that throwing those numbers without any other > explanations about your test environments, SQL, > tables, etc is a good practice. Do you think that you > always get what you pay when you spend thousand of > dollars in software and services without leaving you > any other choices? > > I know that you did not put bad intentions behind your > comment but should be more careful and precise. > > Whoever is interested on some benchmarks can go to: > http://www.mysql.com/information/benchmarks.html > > There is an interesting article comparing Ms-SQL, > Oracle, DB2 and MySQL in: > http://www.pcmag.com/article2/0,4149,7279,00.asp > > Sincerely, > > Francisco > > --- Mary Stickney <[EMAIL PROTECTED]> 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.... > > > > -----Original Message----- > > From: Francisco [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 8:47 AM > > To: Mary Stickney; Elizabeth Bogner; > > [EMAIL PROTECTED] > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > Hi, > > > > I am beging using MySQL for quite a while and it is > > a > > very good choice if you don't really need stored > > procedures. MySQL provides a pretty good > > implementation of a subset of MySQL-92, performance > > is > > great, it is cross-platform, provides transactions, > > and its price... well is free. > > > > Hope it helps. > > --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > > > > > It doesn't suport alot of differnt things.... > > > it dosent have store procedures , dosent have a > > > complete SQL command set... > > > > > > I am using it becasue I am being forced to... > > > > > > > > > > > > -----Original Message----- > > > From: Elizabeth Bogner > > > [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, August 15, 2002 9:25 PM > > > To: [EMAIL PROTECTED] > > > Subject: MySQL vs. Oracle (not speed) > > > > > > > > > > > > A company I work with is in the process of > > upgrading > > > its databases from > > > some > > > motheaten system to something current. My > > impression > > > is that they > > > want to go with Oracle, and I'm not sure if this > > is > > > based on anything > > > other than being impressed with the size and > > > presumed quality support > > > of Oracle. I'd like to encourage them to at least > > > seriously consider > > > using > > > MySQL instead. > > > > > > I don't think that speed is a huge factor here; we > > > do a lot of XML > > > publishing > > > and content management, but at most we'd have > > > several gigabytes of > > > data and several dozen simultaneous users, so well > > > within the > > > capabilities > > > of MySQL. I've looked at various things I could > > > find, like the benchmarks > > > pages (probably not relevant) and the MySQL myths > > > page, which was > > > somewhat helpful, but I couldn't find anything > > more > > > along the lines of > > > "How to Convince my Management to go with MySQL." > > I > > > don't even know > > > what to expect from them, but I'm imagining > > they'll > > > say, "But MySQL > > > doesn't support sub-selects," to which I can > > reply, > > > "But you can write > > > most of those as joins anyway, so it won't matter > > > because the software > > > will all be written from scratch." Etc. > > > > > > Are there pointers anyone can give me? > > > > > > E. Bognewitz > > > > > > > > > > > > --------------------------------------------------------------------- > > > 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 > > > > > > > > > __________________________________________________ > > Do You Yahoo!? > > HotJobs - Search Thousands of New Jobs > > http://www.hotjobs.com > > > > > __________________________________________________ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com > > > --------------------------------------------------------------------- > 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