Thanks Martin, though I'm somewhat confused by your message - there are no joins in the query (unless the longtext s thought of that way) and the Explain seems to indicate the query is using the ItemsById primary index (which is what I would expect).
Patrick myList<http://www.mylist.com/> - everything you could possibly want (to buy) From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: Monday, October 25, 2010 3:53 PM To: Patrick Thompson; gto...@ffn.com; mysql@lists.mysql.com Subject: [SPAM] RE: mySql versus Sql Server performance Importance: Low Patrick- you'll want to determine if your predicates are implementing indexes or FTS with EXPLAIN statement http://dev.mysql.com/doc/refman/5.1/en/using-explain.html you'll need a plan to determine if the join-type of resultsets returned from the Queries are NL, Hashed or sort-merge http://en.wikipedia.org/wiki/Join_%28SQL%29 Each one has pros/cons dependening on the cardinality of the inner resultset vs the outer resultset hth Martin Gainty ______________________________________________ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > From: patrick.thomp...@channelintelligence.com > To: gto...@ffn.com; mysql@lists.mysql.com > Date: Mon, 25 Oct 2010 15:31:26 -0400 > Subject: RE: mySql versus Sql Server performance > > Query: > > SELECT * > FROM Item > WHERE CollectionID = 'a0d3937b-f5a8-0640-dec8-bdd60f7f4775' AND ExternalID = > 'fred1' > > Explain Extended: > > select '17304' AS `ID`,'fred1' AS > `ExternalID`,'a0d3937b-f5a8-0640-dec8-bdd60f7f4775' AS `CollectionID`,NULL AS > `ItemTypeVersion`,'<Item > xmlns="http://cipl.codeplex.com/CIPlItem1.xsd";><Valid>1</Valid><ItemStatus>100</ItemStatus><ExternalID>fred1</ExternalID><ModifiedDate>2010-10-25T15:06:55.7188551-04:00</ModifiedDate><PersonType > > xmlns="http://cipl.codeplex.com/CIPlOther1.xsd";><Address><USAddressType><City><String>Celebration > > 1</String></City><Country><String>USA</String></Country><State><String>FL</String></State><Street><String>1170 > Celebration blvd > 1</String></Street><Zip><Int32>34748</Int32></Zip></USAddressType></Address><AlternateAddresses > Count="2"><USAddressType><City><String>Celebration > 1</String></City><Country><String>USA</String></Country><State><String>FL</String></State><Street><String>1170 > Celebration blvd > 1</String></Street><Zip><Int32>34748</Int32></Zip></USAddressType><USAddressType><City><String>Seattle > 1</String></City><Country><String>USA</String></Country><PhoneNumbers > Count="2"><PhoneNumberType><AreaCode><Int32>206</Int32></AreaCode><Number><Int32>7819281</Int32></Number><Tags > Count="1"><String>never answered > 1</String></Tags></PhoneNumberType><PhoneNumberType><AreaCode><Int32>206</Int32></AreaCode><Number><Int32>9991971</Int32></Number><Tags > Count="1"><String>cell > 1</String></Tags></PhoneNumberType></PhoneNumbers><State><String>WA</String></State><Street><String>12070 > Lakeside pl > 1</String></Street><Zip><Int32>98126</Int32></Zip></USAddressType></AlternateAddresses><CreateDate><DateTime>2010-10-25T15:06:55.7168549-04:00</DateTime></CreateDate><Name><String>fred1</String></Name><Tags > > Count="4"><String>first</String><String>second</String><String>third</String><String>1</String></Tags></PersonType></Item>' > AS `ObjectText`,'2010-10-25 15:06:55' AS `EnteredDate`,'2010-10-25 15:06:55' > AS `LastModDate` from `ciplitemwell0404`.`item` where > (('a0d3937b-f5a8-0640-dec8-bdd60f7f4775' = > 'a0d3937b-f5a8-0640-dec8-bdd60f7f4775') and ('fred1' = 'fred1')) > > Explain: > > 1, 'SIMPLE', 'Item', 'const', 'PRIMARY,ItemsByID', 'PRIMARY', '889', > 'const,const', 1, '' > > > Table definition: > > CREATE TABLE `ciplitemwell0404`.`item` ( > `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, > `ExternalID` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '', > `CollectionID` varchar(40) CHARACTER SET utf8 NOT NULL, > `ItemTypeVersion` varchar(255) CHARACTER SET utf8 DEFAULT NULL, > `ObjectText` longtext NOT NULL, > `EnteredDate` datetime NOT NULL, > `LastModDate` datetime NOT NULL, > PRIMARY KEY (`CollectionID`,`ExternalID`), > UNIQUE KEY `ID` (`ID`), > KEY `ItemsByID` (`CollectionID`,`ID`) USING BTREE > ) ENGINE=InnoDB AUTO_INCREMENT=29687 DEFAULT CHARSET=latin1; > > > This is just the retrieve side - which seems to be around 1.5 times slower > than the equivalent Sql Server numbers. > > The update is much slower - 3 to 5 times slower depending on the record size. > It makes sense to me to focus on the retrieve, maybe the update is just a > reflection of the same problems. > > > Patrick > myList - everything you could possibly want (to buy) > > > -----Original Message----- > From: Gavin Towey [mailto:gto...@ffn.com] > Sent: Monday, October 25, 2010 2:00 PM > To: Patrick Thompson; mysql@lists.mysql.com > Subject: RE: mySql versus Sql Server performance > > MySQL and most other databases require adjustment of server settings, and > especially of table structures and indexes to achieve the best performance > possible. > > If you haven't examined index usage for the queries you're running, or > adjusted server memory settings from defaults, then it's no surprise you > would get poor performance. > > I don't have the inclination to dig through your code; however, if you > extract the actual queries you are running, then run EXPLAIN <query>; that > will show how it's using indexes. You can put that information here, along > with the SHOW CREATE TABLE <table> \G output for all tables involved, and > someone here should be able to help diagnose why the queries might be slow. > > Regards, > Gavin Towey > > > -----Original Message----- > From: Patrick Thompson [mailto:patrick.thomp...@channelintelligence.com] > Sent: Monday, October 25, 2010 6:38 AM > To: mysql@lists.mysql.com > Subject: mySql versus Sql Server performance > > I am running an open source project that provides an abstraction layer over a > number of different stores. I am puzzled by performance numbers I am seeing > between mysql and sql server - a brief discussion is available here > > http://cipl.codeplex.com/wikipage?title=Data%20Provider%20Comparison > > The statistics were generated using mySql 5.1 and Sql Server 2008 on a > machine with the following specs: > > OS Name Microsoft Windows 7 Professional > System Model HP Compaq nc8430 (RB554UT#ABA) > Processor Intel(R) Core(TM)2 CPU T7200 @ 2.00GHz, 2000 Mhz, 2 Core(s), 2 > Logical Processor(s) > Installed Physical Memory (RAM) 4.00 GB > Total Virtual Memory 6.75 GB > Page File Space 3.37 GB > Disk 120GB SSD with 22GB available > > If this isn't the right place to ask this question, can someone point me to > somewhere that is. > > Thanks > > > Patrick > Are you using... > myList<http://www.mylist.com/> - everything you could possibly want (to buy) > Let me know if you can't find something > > > ________________________________ > The information contained in this email message is considered confidential > and proprietary to the sender and is intended solely for review and use by > the named recipient. Any unauthorized review, use or distribution is strictly > prohibited. If you have received this message in error, please advise the > sender by reply email and delete the message. > > This message contains confidential information and is intended only for the > individual named. If you are not the named addressee, you are notified that > reviewing, disseminating, disclosing, copying or distributing this e-mail is > strictly prohibited. Please notify the sender immediately by e-mail if you > have received this e-mail by mistake and delete this e-mail from your system. > E-mail transmission cannot be guaranteed to be secure or error-free as > information could be intercepted, corrupted, lost, destroyed, arrive late or > incomplete, or contain viruses. The sender therefore does not accept > liability for any loss or damage caused by viruses or errors or omissions in > the contents of this message, which arise as a result of e-mail transmission. > [FriendFinder Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, > FriendFinder.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com >
