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:[email protected]]
Sent: Monday, October 25, 2010 2:00 PM
To: Patrick Thompson; [email protected]
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:[email protected]]
Sent: Monday, October 25, 2010 6:38 AM
To: [email protected]
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/[email protected]
