Eh Darryl,

The comments about MySQL and it's ref-integrity simply don't
make sense at all.

Indices, however, have nothing to do with a _logical_ design -
constraints do.

Indices are available ONLY for speed. Therefor they should be
as fast as possible.

Heck, in an environment where we would have the ideal super-
server (not the architecture) with unlimited clock-cycles, we
wouldn't even need indices at all! (but we would need constraints).

It's the physical implementation of Firebird that shows us that
we need indices - to be correctly and no offence intended, it's
the _lack_ of a _decent_ implementation (actually and currently,
the lack of being able to write a decent implementation) of the
_logical_ relation model that throws the need upon us for indices.


With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

> > So you can go with the DBMSs that let you blindly define
> case-insensitive
> > searches for everything and cop the resulting slow searches; or you
> can
> > design your case-insensitive searches with more attention to
> > performance. Where performance is the issue, there is nothing
> faster than
> > an indexed search on a proxy (aka shadow) column.
> .
> .
> > The argument that Firebird should bend its implementation of
> > case-insensitive indexing to align with inferior and less flexible
> > implementations in competitor products appears specious to me.
>
> When I read the above comments, there was something about them that
> bothered me but I couldn't figure out what it was. Then it finally
> dawned on me what this argument reminded me of. For years MySQL users
> complained about its lack of referential integrity. MySQL would claim
> in their documentation that for the sake of speed, referential
> integrity was a bad thing. It was better to force the user to "roll
> their own" referential integrity to prevent slowing down the database.
> Here's a quote from MySQL documentation:
>
> =======================================================
> FOREIGN KEY is sometimes used as a constraint check, but this check is
> unnecessary in practice if rows are inserted into the tables in the
> right order.
>
> A properly written application will make sure internally that it is
> not violating referential integrity constraints before proceding with
> a query. Thus, additional checks on the database level will only slow
> down performance for such an application.
> ========================================================
>
> A similar argument is being made here about indexing.
> Case-insensitivity slows down the database so the user should create
> their own indexing mechanism so as not to impact speed. The lack of
> case-insensitivity isn't a shortcoming. Its a feature!
>
> If I want my car to go as fast as possible, I could get better
> performance by disconnecting the air conditioner. But I don't need my
> car to go as fast as possible. I need it go fast enough to get the job
> of transporting me to my destination. Once its able to do that, I'm
> willing to trade additional speed for features that provide comfort
> and ease-of-use.
>
> If the goal of Firebird developers is to make the fastest possible
> database then my suggestions about case-insensitivity should be
> ignored. If the goal is to spread the use of Firebird and increase its
> base, then you may want to consider that other developers feel the way
> I do.
>
> Helen called my suggestions specious. I prefer the term deference to
> the common expectations of most database developers.
>
> Darryl