How can I improve this endless query?

Question

I've got a table with close to 5kk rows. Each one of them has one text column where I store my XML logs

I am trying to find out if there's some log having

<node>value</node>

I've tried with

SELECT top 1 id_log FROM Table_Log WHERE log_text LIKE '%<node>value</node>%'

but it never finishes.

Is there any way to improve this search?

PS: I can't drop any log

Answer 1

A wildcarded query such as '%<node>value</node>%' will result in a full table scan (ignoring indexes) as it can't determine where within the field it'll find the match. The only real way I know of to improve this query as it stands (without things like partitioning the table etc which should be considered if the table is logging constantly) would be to add a Full-Text catalog & index to the table in order to provide a more efficient search over that field.

Here is a good reference that should walk you through it. Once this has been completed you can use things like the CONTAINS and FREETEXT operators that are optimised for this type of retrieval.

Answer 2

Apart from implementing full-text search on that column and indexing the table, maybe you can narrow the results by another parameters (date, etc). Also, you could add a table field (varchar type) called "Tags" which you can populate when inserting a row. This field would register "keywords, tags" for this log. This way, you could change your query with this field as condition.