Re: query efficiency - Can I speed it up?

On Thursday 01 February 2007 12:01, ann hedley wrote:
> Thanks but no. I think because it's description~ (like) rather than
> description= therefore it has to search the whole string in each
> description.
>
> From the output below it seems to search the 'go' table once, cost 266,
> then for each record found searches the 'gotcha' table (cost 46809). So
> it's the gotcha part of the query I need to speed up, yes?
>
> Maybe I just need a clever bit of PHP that checks the cost and if it's
> excessive asks for a more specific search string?
>
> Thanks
>
> nemdb3=# explain analyse select pept_id,description,confidence from
> gotcha inner join go on (gotcha.go_term=go.go_term) where go.description
> like 'tRNA acetylation' and ( spid='ALP' or spid='ASP' or spid='DIP' or
> spid='GPP' );

Ann,

You might think about full-text searching of the descriptions. It may be
quite a bit faster than a "like" search. It may be worth looking.

Sean