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
