I believe MySQL doesn't do a lot of the optimizations that MSSQL does. However, you can do it manually (I think) by playing around with both the order of the where clause and the order of the join clause
The where clauses go in order, so you want to use the first part of the where clause to get rid of as many records as possible (ie. use the most restrictive where clause first and then go down from there). That way, you minimize the quantity of data getting through the first where clause and not the second or third, or nth. Also, MySQL has a really wimpy default configuration (I can't figure out why). Here is my /etc/my.cnf (I don't know what the equivalent is on windows) [mysqld] set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = table_cache=256 set-variable = key_buffer=256M set-variable = tmp_table_size=256M set-variable = interactive_timeout=7200 set-variable = wait_timeout=40 set-variable = max_connections=200 # innodb stuff added 05/16/02 innodb_data_file_path = ibdata:500M set-variable = innodb_buffer_pool_size=350M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_files_in_group=7 set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=4 default-table-type=innodb -----Original Message----- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:32 AM To: Francisco; Elizabeth Bogner; [EMAIL PROTECTED] 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
