Search code examples
mysqlsearchplural

MySql plural search without Fulltext

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

Solution

  • 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.