[BioSQL-l] still cramming genbank

[Hilmar Lapp]

The problem is the wildcard at the beginning of the search string. 
This prevents it from using an index, using a full table scan 
instead. (A full table scan over 15 Mio entries should still come 
back after a while!) (If you can rewrite the query to only have the 
wildcard at the end, it should be pretty fast.)

MySQL does have text indexing though as Elia mentions. If I remember 
correctly, your version is around 3.23.50, which should have this 
capability. Check the MySQL docs for 'MATCH AGAINST'.

	-hilmar

On Sunday, November 10, 2002, at 10:12 PM, Elia Stupka wrote:

>> SELECT * FROM sometable WHERE someattribute LIKE '%someword%';
>>
>> The search takes forever.  I was wondering if other people have 
>> experienced
>> this problem, and what kinds of things I should look into in order 
>> to speed
>> things up and make the database usable.
>
> "LIKE" searches always take forever, because of the non-exactness. Best
> thing is to try and eliminate where possible the actual need for
> "LIKE" searches. Better to create for example a full-text index 
> (you can
> do that running a separate instance of MySQL 4) and use that to 
> search...
>
> Elia
>
> ********************************
> * http://www.fugu-sg.org/~elia *
> * tel:    +65 6874 1467        *
> * mobile: +65 9030 7613        *
> * fax:    +65 6779 1117        *
> ********************************
>
>
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l@open-bio.org
> http://open-bio.org/mailman/listinfo/biosql-l
>
--
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------