Re: How to boost performance of queries containing pattern matching characters

On 14/02/11 06:59, Gnanakumar wrote:
>
> How can we boost performance of queries containing pattern matching
> characters?

> QUERY:  DELETE FROM MYTABLE WHERE EMAIL ILIKE '%domain.com%'

> As it is clear from the above query, email is matched "partially and
> case-insensitively", which my application requirement demands.

Well, for that exact pattern you're not going to find an index that's
much help. Do you really need something so wide-ranging though? The
above will match all of the following:

user1@domain.com
user2@sub.domain.com
user3@domain.com.au
user4@unrelated-domain.com
user5@unrelated-domain.com.au
user3@sub.domain.com.au
user4@sub.unrelated-domain.com
user5@sub.unrelated-domain.com.au
user6@sub.unrelated-domain.completely-wrong.com

Is that really what you are after? Or, did you just want to match:
   user1@domain.com
   user2@sub.domain.com

--
   Richard Huxton
   Archonet Ltd