[BioSQL-l] Full text indexing/Searching in MySQL

[Roy Chaudhuri]

Hi Mick,

> Has anyone implemented full text indexing/searching for BioSQL in MySQL,
> either using MySQL's full text features or any other solution?

I've kind of done this. The trouble is that full text is only 
implemented on the non-transactional MyISAM tables, not InnoDB (it has 
long been promised for InnoDB, but no sign yet). My hack solution was to 
parse out the fields I was interested in (feature tags such as gene and 
product) and include them in a separate MyISAM table, cross-referenced 
to BioSQL using seqfeature_id. This involves duplicating data (which is 
a bad thing), but should be okay if database updates are infrequent. I 
mimic atomic changes by building an updated version of the MyISAM table 
separately, then switching to use the new version at the same time as I 
commit the BioSQL updates.

There's also Sphinx (http://www.sphinxsearch.com), which is a plug-in 
that can implement full-text searches in InnoDB, but I haven't 
experimented with that so have no idea how well it works.

Cheers.
Roy.