2

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 ;-)

1 Answer 1

2

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;
5
  • I can see how this makes good sense and simplifies the query (single column). Does this "break" 1NF though by duplicating the "real" primary values in another table?
    – Jillian
    Aug 14, 2014 at 16:54
  • I guess it does to a degree, but if you look at from the point of view that the "real" value is also an alias of it's self, then it may not. You could remove them from the alias/search table and do a union selection.
    – MickyT
    Aug 14, 2014 at 19:18
  • 1
    No, using one table's PK as a FK in another table does not break 1NF. Having a delimited list as a column's value breaks 1NF. Sep 19, 2014 at 3:00
  • If you define another column on your alias table and populate it with REVERSE(alias) and index it, too, you will be able to do %keyword lookups (or should I say "drowyek%" lookups). Not a MySQL expert so this my take a little work. Sep 19, 2014 at 3:03
  • @Jillian, no this design does not break 1NF. Your delimited strings version breaks 1NF. Sep 19, 2014 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.