I have a MySQL table (~10,000 rows) with a pk id
(int) column and a name
(varchar) column (as well as a few other unimportant columns for this question).
The name
column contains unique values that are "official" names however some of the items have a commonly known alias, abbreviation or alternate spelling. I'm guessing maybe 10-15%.
I have an existing query to the effect of:
SELECT id, name
FROM items
WHERE name
LIKE '%keyword%'
ORDER BY name ASC
LIMIT 15;
to allow users to search for items with a quick type-ahead widget.
Since some items will have aliases I'd like users to be able to search and get matches based on the alternate names. My current plan was to add another column to the table called alternates
containing delimited alternatives.
e.g. (the delimiter :
could be whatever)
id | name | alternates
------+-----------------------+------------------------
1 | Computer | PC : Mac
2 | Kraft Dinner | KD
3 | Chesterfield | Couch : Sofa : Sete
4 | BMW | Beamer
5 | Microsoft SQL Server | MSSQL Server : SQLServer
and then alter my query to:
SELECT id, name
FROM items
WHERE name LIKE '%keyword%'
OR alternates LIKE '%keyword%'
ORDER BY name ASC
LIMIT 15;
Is this an "accepted" practice or is there a preferred or better performing way to do this?
I apologize if this seems simple to the SQL Pros but Googling for "SQL" and "alias" shows hundreds of non-applicable results (surprise, surprise ;-)