I will agree from experiance. It is MUCH faster, when you get into larger data sets, to drop FK/RI all together, and handle the logic at the application level. However, "indexes" in general are another story. That starts to become database specific and has to do with the ways in which you can manipulate the query plan per DB implementation (Oracle, Sybase, MSSQL, etc.). In general though, for mass inserts, it is faster to drop all indexes, insert/update, and then re-create the index. I use this technique on a couple apps with GREAT success.
<br><br>I think the DB use issues, IMHO, comes from and depends on the applications purpose. Most Drupal apps are blogs/news content/etc. and are READ/SELECT intensive, thus most DB will do, and it's just as good to use MySQL, SQLite, etc. However, when you get into transaction/OLAP/e-commerce heavy type apps with many simultaneous UPDATE/INSERTS/transactions.....this, I find, is where the scalability/usability of MySQL, comes into play.
<br><br>With that said...I wonder if we should have an option to not use RI. But...dont' get me wrong, I want RI in Drupal, and I think RI needs to go into core, and the above situation I have discussed should be considered a minority and something dealt with by the developer/enduser.
<br><br><br><div><span class="gmail_quote">On 1/21/07, <b class="gmail_sendername">Khalid B</b> <<a href="mailto:kb@2bits.com">kb@2bits.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<span class="q">On 1/20/07, <b class="gmail_sendername">Karoly Negyesi</b> <<a href="mailto:karoly@negyesi.net" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">karoly@negyesi.net</a>> wrote:</span>
<div><span class="q"><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
> Drupal is kindof database independent. Though no-one has managed to integrate<br>> sqlite because of 'things' (locking) that Drupal requires.<br><br>My hopes are that SQLite support will happen with Drupal 6. Tadej Ba&#353;a has written the code and he says he is running Drupal with it since
4.5 which is quite believable. He also volunteers to be the maintainer: <a href="http://lists.drupal.org/pipermail/development/2007-January/021806.html" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://lists.drupal.org/pipermail/development/2007-January/021806.html
</a> My hopes are that Drupal will run out of the box, without any setup whatsoever. LOCKs are on their way out, killes is nurturing the no lock patch I abandoned. And even if they do not die, they are there only to avoid some races which simply do not happen on the small sites that are targets for SQLite (ie. quite small sites).
</blockquote></span><div><br>Wasn't the other problem is that SQLite does not have some ALTER variant that we use, so updates to the schema not possible? Has this been recently solved too? If so, then good news.<br></div>
<br>
<br><br>Back on topic, referential integrity is the way forward. Not only does it make us friends with the PostgreSQL folk, but it is good practice in general to have it, regardless of the underlying database engine.<br>
<br>
The debate about RI is a non-debate. It is a historical issue. There was a database used by hosting companies called mSQL (mini SQL) and it was not free, MySQL stepped in to fill the gap with a free database (as in cost-free initially), with the same syntax, ...etc.. MySQL's popularity skyrocketed, then it became free (as in liberty). They argued that RI is not necessary since web apps are mostly read intensive, and they shined in the read intensive arena.
<br><br>Now, true databases all have RI. MySQL themselves had InnoDB, and most hosting companies for the last 2 years or so support InnoDB. If only Oracle would not have nabbed that, it would have been the way forward. Now they are developing Falcon and solidDB, ...etc.
<br><br>Regardless, RI is needed. <br><br>+1.<br></div>
</blockquote></div><br>