Hey Daniel... OK, I admit it..we are a MS house!!! And not to get into a platform war :-) But from tests I have never seen mySQL outperform SQLServer under stress... Though I will look into it and see what the deal is - you never know maybe I can get them to stop using MS...erm not likely. The indexing engine in 2K is a hell of a lot faster and more logical than 7, it was rewritten and remodelled for this purpose. This I know from the MS SQL Server Engineering Docs.
"Theortically a database tables primary key should always be the >Clustered Index as its this index (at least in SQL Server)" This is from experience and results coming back from a a multi-million record database - though as you note in some cases this may not be the case - especially with FarCry schema as its not as complex as some (which is good!)...If you look at execution plans etc you can see improvements on indexes of PK's rather than indexes on it varchars.. "MSSQL DBA (2000) Ditto. (MCDBA)....You gonna take Yukon when it fnally arrives :-) Again, nothing against doc, its just that as we both know tweaking and finetuning SQL Server is literally an art and each DBA may have different ways of doing it. FarCry can benefit from simple indexing and indeed, if I find any big wins I will be sure to post. N -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Daniel Morphett Sent: 10 February 2004 02:15 To: FarCry Developers Subject: [farcry-dev] Re: Database Indexing Strategies for FarCry -- technote At 06:00 PM 2/9/2004 +0000, you wrote: >Hey, > >Good doc, and since SQL DB is my area of expertise :) there are key >points missing in this doc which need to be added or at least brought >to light (some of which I reiterate from the Doc). First off, MySQL >may have a very fast retrieval of rows, but in terms of performance >against Enterprise Solutions such as Oracle and SQL Server - its >nowwhere near as fast.. I doubt that. check the following tests. mysql wins hands down. http://www.mysql.com/information/benchmarks-old.html If you have an extremely large db, this may alter things somewhat, but it's hard to imagine a farcry db getting that big. btw, I have no attachment to any particular platform, am a certified MSSQL DBA (2000), so that is my expertise, but I have been impressed by the speed at which mysql functions: it runs like a demon compared to mssql. >It must also be noted that SQL 7 and SQL 2000 peformance on indexing >etc is vast....7 being slower :-) What do you mean by this? >Theortically a database tables primary key should always be the >Clustered Index as its this index (at least in SQL Server) Not true. Where did you get that idea? Read the following article for further enlightenment. http://www.databasejournal.com/features/mssql/article.php/1466951 >which is used as the pointer for the inbuilt Query Optimiser Engine..... >you can only have one clustered index per table so your primary key >should always be placed on a column which unlikely to change i.e. >Unique. If you modify a table at a later date and find that that new >column has greater potential for a clustered index then you will have >trouble as you can't drop a clustered index and recreate it later - you >have to rebuld the entire table - not nice. If your table is huge this may be an issue, but tables under 100,000 rows shouldn''t be too much of a problem. > Its good practice to only index columns which contain integer values > as character based indexes have a much larger overhead. true. But I think you will find that even though I recommend the indexing of character columns, you will be better off with the indexes than without them. >Over indexing can in fact degrade DB performance, you should only ever >index columns which you know are used either frequently or constantly. >Any indexes which are surplus to requirements slow data modifications >and cause unnecessary I/O reads when reading pages, and they also waste >space in your database. noted. This was also noted in my article >As well as Indexing also consider that you need to keep your Statistics >up to date...on SQL 2000 this should be handled automatically but in >SQL7 you have to trigger it off using custom scripts or by the system sproc : >sp_updatestats true >If you really need to fine tune, then run a SQL Profiler to find what >SQL Blocks are taking the longest to load and then you get a feel of >what needs to be indexed etc.... > >Hope this helps.... > >N > > > > > > > > > > >Daniel Morphett wrote: >>You're most welcome. Glad the article was useful to you. >>Daniel >>At 11:48 AM 2/9/2004 +1100, you wrote: >> >>>Thanks David for doing the dirty work that most of us would never >>>have got around to doing. If you have any more of those hidden gems >>>please don't hesitate to share :) >>> >>>- tim >>> >>>Brendan Sisson spoke the following wise words on 9/02/2004 10:35 AM EST: >>> >>>>Daniel Morphett provides an introduction to database indexing and >>>>some recommendations for indexing your FarCry CMS databases. >>>>http://farcry.daemon.com.au/go/objectid/8801C069-D0B7-4CD6-F95BA22C4 >>>>E521DBE >>>> >>> >>> >>> >>> >>>--- >>>You are currently subscribed to farcry-dev as: [EMAIL PROTECTED] >>>To unsubscribe send a blank email to >>>[EMAIL PROTECTED] >>> >>>MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia >>>http://www.mxdu.com/ + 24-25 February, 2004 >> >>Daniel Morphett >>Web Developer, DBA >>www.daemon.com.au >> > >--- >You are currently subscribed to farcry-dev as: [EMAIL PROTECTED] To >unsubscribe send a blank email to >[EMAIL PROTECTED] > >MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia >http://www.mxdu.com/ + 24-25 February, 2004 Daniel Morphett Web Developer, DBA www.daemon.com.au --- You are currently subscribed to farcry-dev as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com --- You are currently subscribed to farcry-dev as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
