On Wed, Aug 13, 2008 at 9:20 AM, Sheeri K. Cabral <[EMAIL PROTECTED]> wrote:
> On 8/13/08, Mark Leith <[EMAIL PROTECTED]> wrote:
>>
>> Why not function based indexes, as Baron pointed to as the last option in
>> his previous reply?
>>
>> I've wanted function based indexes since the dawn of time in MySQL - well,
>> at least since Oracle 8i anyway.. ;)
>>
>> http://www.akadia.com/services/ora_function_based_index_2.html
>>
>> In MySQL Worklog parlance, they are 'Functional Indexes', or 'Calculated
>> Indexes', a public worklog for this is here:
>>
>> http://forge.mysql.com/worklog/task.php?id=1075
>>
>> This looks like exactly what you guys are talking about, and does not give
>> the need to store the value in another separate column, with another index
>> etc. (... more space)..
>
> From what I understand, "functional indexes" are very close to "materialized
> views." (so yeah, we're back to views.)
There's a really important difference.
Example:
create table urls (
url varchar(1024),
);
insert into urls(url) values('www.drizzle.com');
A materialized view of this, with an index on the crc32 of the url,
looks like the following, with made-up syntax for clarity:
create materialized view mat_urls as select url, crc32(url) as
crc32url, key(crc32url) from urls;
The data structure of that is probably stored in a 'hidden' table that
looks like this behind the scenes:
create table __hidden_mat_urls (
url varchar(1024),
crc32url int,
key(crc32url)
);
Picture the b-tree of that in your mind: integers in the tree, leaf
nodes point to rows that have the given integer stored in them.
The functional view on the other hand, never needs to store the crc32
value with the row. You just have a b-tree of integers, and the leaf
nodes point to the rows whose urls have the given crc32 value. But
there's no integer stored with the url in the row.
So there's a big difference in terms of storage.
Maybe it doesn't have to be this way, but AFAIR it's how SQL Server
does it. Anyone know of a smarter implementation?
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
