Searching KEYWORD in database

Anon

I wrote a search query, it works but it's not perfect: if I search the word DOG the query result gives me only DOG and not DOGS nor MYDOG and that is how I want it. THE PROBLEM IS that it doesn't find DOG when there is a ',' or a '.', i.e. it doesn't find 'DOG;' and it doesn't find DOG when DOG is the first word of a TEXT field of the database.

HOW CAN I SOLVE THIS PROBLEM? I put the query I made here under:

SELECT * FROM db_articles WHERE text LIKE '% ".$keyword." %'

Anon

Take out the spaces before/after the two % signs

saloon12yrd

Hi Nico,

using LIKE '%' . $string . '%' is bad, really bad. It doesn't even make use of indices, so you could search a flat file at the same speed.
Check the manual for FULLTEXT keys.
However this won't solve your problem on "dog" since search queries need to have four or more letters. There are other deficiencies as well but its by far better than LIKE.
However if the DB is small and queries rare, remove the whitespace between the string and the %'s 😉

Greets,

Dominique

[deleted]

FULLTEXT indexes are useless, because they only match on complete words.
if you are looking for 'dog' it will find 'dog', but not 'hotdog'.

Read the article about 'slapping together a search engine'. With a small modification you can make a very effective and fast search engine.

saloon12yrd

Your example falls into what I meant by "other disadvantages". To get away from "dog" which won't be found anyways, here's a better example:

Search "friend":
It will be found if the text contains "friendship".
It will NOT be found if the text contains "girlfriend".

Greets,

Dominique

nicdp

I don't want to find neither "girlFRIEND" neither "friendSHIP", but I want to find FRIEND when it is followed or preceded by PUNCTUATION, ie. ",FRIEND:" I want to find it in this case..

[deleted]

With FULLTEXT, neither friendship NOR girlfriend will be found, FULLTEXT only matches on whole words.

(FYI: MySQL can be instructed to also process words less than four characters, they just default to >3)

[deleted]

Then you can use a regexp of some sort, which is even slower than LIKE.
You could expand your query to include that which you want to find:

SELECT *
FROM table
WHERE field LIKE '% friend %'
OR field LIKE '%,friend.%'
OR field LIKE '%.friend,%'
OR field LIKE '% friend.%'
etc, etc...

but that's not really feasable...

If you build a custom searchengine (as described in the article), you can do all the 'slow' processing of the content once for each record, and from then on you can use simple statements like:

SELECT * FROM table WHERE keyword = 'friend';

which will then return a list of records that contain 'friend'.

saloon12yrd

Its the same...
"friend." will be found.
",friend" won't.

A 'good' text would spell ", friend" which again would be found.

Dominique

saloon12yrd

Wrong. My searchengine does find "friend" in FRIENDship. It won't be found if "friend" is contained in more than 50% of the rows though, but that's another issue.

Concerning the 3-letter-thing: Sure, you can tweak the code and recompile but if it gets to that you can do anything.

Dominique

saloon12yrd

"rebuild the index" was what I meant.
However you can get rid of the 50% issue by tweaking and recompiling (just one line).

Dominique

[deleted]

Well I just created 20 rows without the word friend in it, and one row with 'friendship'
and it doesn't find it.

All this 'argueing' just proves my point that FULLTEXT sucks bigtime. :-)

saloon12yrd

hu?
Strange thing, my 3.23.43 version works.

[deleted]

3.23.46 here :-)

saloon12yrd

Hmpf, I do something like:
SELECT id FROM table WHERE (MATCH (title, abstract, content) AGAINST ('$s_query'));

Very strange thing it's not working for you...

Dominqiue

[deleted]

lol

I don't complain about MySQL for nothing! 😉

smarlowe

How big is your data set and how big will it get? If it's a few thousand rows, no big deal, if it's a few million, big deal.

I throw queries like this:select lname, fname, ext, dept, dept_no, ms, pname, lvname, phonebook.OID, number, pos, enum from phonebook left join lev2_1 on (phonebook.lvcode=lev2_1.lvcode) left join phonebook_keywords on (enum=phonebook_keywords._enum) where (lower(lname) like '%apache%' or lower(prev_lname) like '%apache%' or lower(fname) like '%apache%' or lower(ext) like '%apache%' or lower(dept) like '%apache%' or lower(dept_no) like '%apache%' or lower(pname) like '%apache%' or lower(ms) like '%apache%' or lower(number) like '%apache%' or lower(pos) like '%apache%' or lower(lev2_1.lvname) like '%apache%' or lower(phonebook_keywords.keywords) like '%apache%')and (lower(lname) like '%php%' or lower(prev_lname) like '%php%' or lower(fname) like '%php%' or lower(ext) like '%php%' or lower(dept) like '%php%' or lower(dept_no) like '%php%' or lower(pname) like '%php%' or lower(ms) like '%php%' or lower(number) like '%php%' or lower(pos) like '%php%' or lower(lev2_1.lvname) like '%php%' or lower(phonebook_keywords.keywords) like '%php%')and (lower(lname) like '%sql%' or lower(prev_lname) like '%sql%' or lower(fname) like '%sql%' or lower(ext) like '%sql%' or lower(dept) like '%sql%' or lower(dept_no) like '%sql%' or lower(pname) like '%sql%' or lower(ms) like '%sql%' or lower(number) like '%sql%' or lower(pos) like '%sql%' or lower(lev2_1.lvname) like '%sql%' or lower(phonebook_keywords.keywords) like '%sql%') order by lower(lname), lower (pname), lower(fname), lower(dept);

at the company phonebook all day, with about 2,000 people in it, and it takes about 1/10th of a second to run each time.

If you're gonna brute force it, and sometimes it's the only answer because indexing results in such a restricted way of searching, just make sure you've got LOTS of shared memory configured for your database to use, and it WILL help.