perform a search query using LIKE statements

[lli2k5]

I'm planning to create search queries using LIKE statements, and not full-text index.

The problem is how would you match the entire word?

If I say LIKE '%table%'.

How would I get it to match just the word table? If I do LIKE '% table %', then the case where table is the first word would not be shown in queries.

[bradgrafelman]

Perhaps using word boundaries in a regular expression pattern?

SELECT `description` FROM `myTable` WHERE `description` REGEXP '[[:<:]]table[[:>:]]'

More information on this page of the MySQL manual.

[lli2k5]

Thanks, I just surfed google and found that too.

Is there a problem with runtime? It seems REGEXP causes the search to run a lot slower?

[bradgrafelman]

Well, not only does REGEXP require the server to think about it a little more, this type of a WHERE statement probably causes a table scan.

I'm definitely no SQL guru, so I'm afraid I can't help much. Perhaps someone more knowledgeable can post about optimizations/different methods of going about something like this.

[Roger_Ramjet]

LIKE will force a tavble scan except when the pattern to match is the first part of the index entry. So just use full text search like you should. Anything else will be slow as a boat.