Details
Minor
Description
Allow a user to create unique constraints of arbitrary length. Only in MyISAM (extending this to InnoDB is a separate task).
MyISAM supports unique constraints of arbitrary length. They are used in queries like
SELECT DISTINCT blob_column FROM some_table;
The server executes this query with the help of a temporary MyISAM table. And this table must be able to maintain uniqueness of all blob_column values. Internally it's implemented as an index over hash values of blobs, with the additional direct value comparison on collisions.
So, MyISAM can do it internally, but there is no way for the end user to create such a unique constraint explicitly. The following statement
CREATE TABLE some_table (blob_column BLOB, UNIQUE(blob_column));
will fail.
This task is about giving the access to this MyISAM feature to the SQL layer, about allowing the end user to create long unique constraints explicitly.
original bug report:
hi guys, i was reading about index... and i have a interesting problem...
i need to check if a file (ok a big row... no problem...) is inside my table...
what i´m thinking....
create table a(b int not null default '',c longblob not null, primary key b)
...
ok no problems....
the problem is... how to know if a file, let´s sai a file of 16MB is inside my table...
first solution is... MD5 and check each row... OK nice work....
but could be a other nicer solution?!
i was thinking something like:alter table a
add index some_index(c) using hash;could this work? since it´s a hash index, i don´t see why should i use a part of c value like c (100) for example...
could check if this is possible? today not... i tryed and it return:
/* SQL Error (1170): Coluna BLOB 'hash_automatico' usada na especificação de chave sem o comprimento da chave */ (in portugues PT_BR)i think that´s all
Gliffy Diagrams
Attachments
Issue Links
- includes
-
MDEV-5795 Full-Column Unique Index
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
humm, nice =)
well i think the problem was sending big quanty of bytes in/out database, but... maybe a function to help index could be nice... i think that many guys implement something like
select count(*) from files where some_hash_field=(length || ';' || some_hash_value_calculated_in_a_script)
if count(*)> 0
select * from files where some_hash_field=(length || ';' || some_hash_value_calculated_in_a_script)
well, the first part is realy nice... but if it return >1 well send file is nicer than read many files with same size...
i don´t know if it could be nice, but implementation is close to hash with any binary char field, since it´s a hash not a btree... (i´m wrong?)
well =) let´s see what happen =)
it´s a feature request, don´t think that´s really needed but a 'formal' or a 'recommended' way to check if a 'file' (blob) is in database could be good, at least in documentation...
hi sergey any idea where the patch about this try was saved? i want to see if it's easy to implement but i don't know how to start
No, I don't. Any anyway, if I would like to do it, I would rather start
from scratch, than from some old incomplete patch.
ok, at least a hash index/unique index could help a lot
this should be done in each storage engine, or index is a general "feature" of mariadb?
in each storage engine. in particular, MyISAM and Aria almost support this already, and that "attempt" that I was referring to was exactly about making them support it fully.
hummm nice, in first step make myisam and aria blob index possible, innodb and others is a second step, right?
Hi Sergei,
I am interested in working on this feature as part of GSoC. From Google search it seems that this feature is really important as many people report problem related to having BLOB/TEXT as primary key or creating index for it.
I have good experience with databases but currently I am new to mariadb source code. I would appreciate if you can help me in getting started.
Thanks
yes, this was discussed for quite a while, and has a long history in MySQL bug database.
there was even an attempt to implement this, but somehow it failed, don't really know why.
I guess, we can give it another try.