MySql plural search without Fulltext

Question

I want to make a plural search on my table but i don't want to use FULLTEXT.I tried FULLTEXT but my table doesn't support it.My query is like:

SELECT
     *
FROM
     items
WHERE
     LOWER(items.`name`) LIKE '%parameter%'
OR LOWER(items.brand) LIKE '%parameter%'
OR LOWER(items.sku) LIKE '%parameter%'

When i search 'shirt' it returns good results when i search shirts i doesn't.Is there a way to make plural search without fulltext

score 1 · Accepted Answer · answered Jan 24 '13 at 10:15

1

I suggest you to create separate table items with MyIsam Engine for items with fields you want to perform search and primary id.

Now you can do full-text search on new table and retrieve ID and based on ID you can retrieve result of fields from main items table.

The additional table for "items" needs to be updated regularly, may be though trigger or automated script.

answered Jan 24 '13 at 10:15

Minesh

2,284
1
14
22

2 Tables? i don't know is there an easier way? – Antonio Papa Jan 24 '13 at 10:29
@AntonioPapa if you don't have any restriction you can just convert existing table to MyIsam and have full-text search. – Minesh Jan 25 '13 at 06:48
Is apache lucene a solution for this problem? – Antonio Papa Jan 25 '13 at 09:28
I have good experience working with Sphinx full-text search engine instead, best suited for MySql. It uses the Porter Stemmer algorithm which means some other kinds of suffixes are also handled, like "running" and "run" will both normalized to "run". – Minesh Jan 25 '13 at 10:33

Arpit · Answer 2 · 2013-01-24T12:52:37.147

0

it will match all those beginning with parameter passed.

SELECT
     *
FROM
     items
WHERE
     LOWER(items.`name`) LIKE 'parameter%'
OR LOWER(items.brand) LIKE 'parameter%'
OR LOWER(items.sku) LIKE 'parameter%'

edited Jan 24 '13 at 12:52

answered Jan 24 '13 at 10:09

Arpit

12,767
3
27
40

make it more clear what should it look like when i type shirts and when i type shirt.It looks like i will search for s character. – Antonio Papa Jan 24 '13 at 10:30
but i don't want that i want when i type shirt and shirts to return the same result – Antonio Papa Jan 24 '13 at 10:45
This will result the same either for shirt or shirts. – Arpit Jan 24 '13 at 13:35
that's not the point i could easily check if the last character is a 's' or not but how about address => addresses , men => men's ect ect you know what i mean :D – Antonio Papa Jan 24 '13 at 14:15

MySql plural search without Fulltext

2 Answers2