Get rows from MySQL table that have a column in common but slightly diffrent value

Question

Might not even be a programming question, but the problem I'm having is I have a big MySQL table with products from different sources (stores) and in many cases it's the same product with different sources.

For example an "iPhone X":

+---------------------+-------+--------------+
| Title               | Price | Source       |
+---------------------+-------+--------------+
| iPhone X            | 1100  | Small Store  |
| iPhone X Phone      | 990   | Medium Store |
| Smartphone iPhone X | 990   | Big Store    |
| iPhone X Smartphone | 1000  | Extra Store  |
+---------------------+-------+--------------+

This is all the same product with different sources, but the Title is a bit different in each case.

What I want to do is when I SELECT one if them I want to return them all.

An idea that came to my head is calculating a hash based on something (the Title?) and storing that, like:

+---------------------+-------+--------------+----------+
| Title               | Price | Source       | Hash     |
+---------------------+-------+--------------+----------+
| iPhone X            | 1100  | Small Store  | SAMEHASH |
| iPhone X Phone      | 990   | Medium Store | SAMEHASH |
| Smartphone iPhone X | 990   | Big Store    | SAMEHASH |
| iPhone X Smartphone | 1000  | Extra Store  | SAMEHASH |
+---------------------+-------+--------------+----------+

That way when I'm fetching an "iPhone X" by something, like a slug getProductsBySlug(slug string) []Product {}, I can SELECT one of them, get the hash and find the rest.

Just not sure how I could calculate such a hash so it's the same for these related products. Maybe it doesn't even make sense and there's a better way to do this.

Any ideas?

Answer 1

You can accomplish what you want using LIKE:

where title like '%iPhone X%'

If you want more efficiency, you can use full text search. However, you need to be careful, because by default "X" would not be included in the index. So, you need to be careful about the minimum word length and stop words.