[BioSQL-l] BioSQL seqfeature_qualifier_value optimization

[Hilmar Lapp]

On Mar 10, 2007, at 1:15 PM, Dmitry Samborskiy wrote:

> Hilmar Lapp <hlapp at gmx.net> wrote:
>
>> Is this a new MySQL 5.0 feature?
>
> No, it seems that it was even before v.4.1, see:
>
> http://dev.mysql.com/doc/refman/4.1/en/create-index.html
>
> And it works with all base DB types: MyISAM, InnoDB, or BDB.

Interesting. I missed that. That's what happens if you don't use  
MySQL :-)

>
>> Does it mean take the first 64 chars
>> and discard the rest for indexing?
>
> Exactly. Below is the citation from the page mentioned above:
>
>> For CHAR, VARCHAR, BINARY, and VARBINARY  columns, indexes can be  
>> created
> that > use only the leading part of column values, using col_name 
> (length)
> syntax to > specify an index prefix length. BLOB and TEXT columns  
> also can be
> indexed, but > a prefix length *must* be given.
>
> I used 64 because it's quite enough in almost all cases (since  
> index is just a
> optimization tool for making search faster).
>
>> Sorry, I'm no longer up-to-date w/ MySQL's feature list since 4.1.x.
>
> Me too. I use mysql-4.1.16-1.FC4.1 (Fedora Core 4).
>
> I don't know how to figure the same index in Oracle/PosgreSQL.
> But I hope it's possible...

You cannot index LOB columns in Oracle. You can index an initial  
substring of a text field in both Oracle and PostgreSQL because you  
can have function indexes. They will only be used though if the query  
uses the exact same function.

	-hilmar

>
> Thanks for your attention.
>
> Best wishes,
>   Dmitry Samborskiy
>
>
>
> --- Hilmar Lapp <hlapp at gmx.net> wrote:
>
>> Is this a new MySQL 5.0 feature? Does it mean take the first 64 chars
>> and discard the rest for indexing?
>>
>> Sorry, I'm no longer up-to-date w/ MySQL's feature list since 4.1.x.
>>
>> 	-hilmar
>>
>> On Mar 8, 2007, at 5:19 PM, Dmitry Samborskiy wrote:
>>
>>> Hello Everybody,
>>>
>>> I use BioPerl-DB and BioSQL project.
>>> I suggest to make one extra index to optimize the search by FT tag
>>> values.
>>>
>>> On MySQL it could be done with
>>>
>>> CREATE INDEX value_ind ON seqfeature_qualifier_value (term_id, value
>>> (64));
>>>
>>> Best regards,
>>>   Dmitry Samborskiy
>>>
>>>
>>>
>>> 	
>>>
>>> 	
>>> 		
>>> ________________________________________________________
>>> Вы уже с Yahoo!?
>>> Испытайте обновленную и улучшенную.
>>> Yahoo! Почту! http://ru.mail.yahoo.com
>>> _______________________________________________
>>> BioSQL-l mailing list
>>> BioSQL-l at lists.open-bio.org
>>> http://lists.open-bio.org/mailman/listinfo/biosql-l
>>
>> -- 
>> ===========================================================
>> : Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
>> ===========================================================
>>
>>
>>
>>
>>
>>
>
>
>
> 	
>
> 	
> 		
> ________________________________________________________
> Вы уже с Yahoo!?
> Испытайте обновленную и улучшенную.  
> Yahoo! Почту! http://ru.mail.yahoo.com

-- 
===========================================================
: Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
===========================================================