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-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
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
