Mysql – select query on unique column AND alternate column of “aliases”

MySQLnaming conventionschema

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 😉

Best Answer

Even though 10,000 rows are not a lot, I would go for a slightly different solution that will perform a lot better.

Keep you master table the same: id, name.

Create a new alias/search table: itemid, alias

Create an index on the alias and a foreign key constraint on the itemid.

Do not have a delimited list of aliases, rather have a row for each individual aliases, as well as the original (authoritive?) name. Then make the searches on this table and remove the wildcard from the beginning of the like comparison. Having a wildcard at the beginning of the like value will make the query ignore the index.

So for the example data the you have you would end up with an alias table like the following:

masterid | alias
--------------------------------------
1        | Computer
1        | PC
1        | Mac
2        | Kraft Dinner
3        | Chesterfield          
3        | Couch 
3        | Sofa 
3        | Sete
4        | BMW
4        | Beamer
5        | Microsoft SQL Server
5        | MSSQL
5        | SQLServer
5        | SQL Server

This could then be queried with:

SELECT i.id, i.name, a.alias searchmatch
FROM items i 
    INNER JOIN itemaliases a ON i.id = a.itemid
WHERE a.alias like 'keyword%'
ORDER BY i.name
LIMIT 15;
Related Question