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
