Search code examples
phpsqloracleperformancesql-like

Speed problems with 3 like statements in 1 query (oracle)


This is a snippet of my query:

and (
    ADDR_FULLTEXT
    like upper(:streets1)
)

Whenever i execute the query this is a part of, it executes in about 0.05 seconds. This is perfect! But, when I do the following:

and (
    ADDR_FULLTEXT
    like upper(:streets1)
    AND
    ADDR_FULLTEXT
    like upper(:streets2)
)

it results in a 20 second query. Why is the difference this big, and how can solve this?

What the rest of the query basically does, is selecting multiple fields from a view (ADDR_FULLTEXT is one of them). There also is another like, just like this one.

Maybe i'm doing something really wrong here, and maybe therefor there is a better way of doing this.

FYI: A PHP loop generates this query, which results from an explode of a search query. Every word is parsed in this query resulting in 1 or more of the same "like" in 1 query. Another like is generated the same way, but this is always 1 like, never more, never less.


Solution

  • try breaking it down into two different queries, and taking the intersection, like:

    SELECT * FROM YourTable WHERE ADDR_FULLTEXT like upper(:streets1)
    INTERSECT
    SELECT * FROM YourTable WHERE ADDR_FULLTEXT like upper(:streets2)
    

    see Intersect