Do you have an index on AdminProducer.taxid and on tempsap.taxid ?
----- Original Message ----- From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 5:31 PM Subject: RE: MySQL vs. Oracle (not speed) > > 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
