Hey Paul, I certainly wasn't saying anything agasint the doc, far from it was good - I was merely indicating other best practices for indexing in general.
Re peformance, I certainly know that under tests (and I mean proper tests) of racks of SQL Servers running Quad Xeons yada yada yada that MSSQL Server is faster then mySQL - especially on complex queries - but that's by the by ;-)! Arent FarCry PK's GUUID's? So, they are technically Varchar() but they are unique. I am comfortable implementing Daniels suggestions and indeed some of my own and if I find that performance does indeed increase I will be sure to post! Thanks for the heads up... N -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Harrison Sent: 10 February 2004 01:20 To: FarCry Developers Subject: [farcry-dev] Re: Database Indexing Strategies for FarCry -- technote Hi Neil - I think your confusing the issue a bit here Neil by bringing up some irrelevant info as far as indexing practices on a farcry db is concerned. Daniels guide is the result of a great deal of experience with building and tuning farcry sites and really represents what Farcry developers - particularly those running large sites - should implement. Just a few comments - RE : MySQL performance. Theres a lot of row retrievals going on in farcry - which is why farcy on MySQL is actually very fast. I dont really have any loyalty to any particular DB, but having developed farcry solutions on Oracle, MySQL and SQL2000, id consider the statement that MySQL as being 'nowhere near as fast' as innacurate.. For the record, my observations as far as farcry is concerned is that on the same server, MySQL is at least as fast if not faster than MSSQL2000, and oracle is slowest. RE: SQL vs SQL 2000 indexing perfomance. Well whatever, indexing still needs to occur. RE :" Its good practice to only index columns which contain integer values as character based indexes have a larger overhead ". This is irrelevant in terms of farcry - the fact is farcry deals with char primary keys, not integers and they simply must be indexed regardless. Re : 'Over indexing can in fact degrade DB performance, you should only ever index columns which you know are used either frequently or constantly. ' Yep Daniel raised this issue as well. Daniels reccomendations however do not represent over indexing, so you can feel comfortable implementing his suggestions. Paul >>> [EMAIL PROTECTED] 2/10/2004 2:00:30 AM >>> 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.. It must also be noted that SQL 7 and SQL 2000 peformance on indexing etc is vast....7 being slower :-) Theortically a database tables primary key should always be the Clustered Index as its this index (at least in SQL Server) 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. Its good practice to only index columns which contain integer values as character based indexes have a much larger overhead. 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. 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 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-F95BA22C4E521DBE >>> >> >> >> >> >> --- >> 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 --- 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
