MySQL: searching with joins or delimited list

longish

[tkroll]

I'm trying to implement a solution where a given company can set a list of the coutries in which it has branches.

My first thought is this:

'company' (id, co_name)
'location' (id, co_id, loc_code)

And when the customer want to know if a company has a location in Belize:

select c.co_name from company c left outer join location l on l.co_id = c.id where l.loc_code = 'BZ';

My second thought is adding loc_code to company:

'company' (id, co_name, loc_code)

and having loc_code hold a list of values like 'BZ,CA,CY,DE,'.

The query then becomes:

select co_name from company where loc_code like '%BZ,%';

---

What are the performance issues here? I know solution 1 is properly normalized, but it does some huge joins when the company table has 10,000 entries with each having 10 locations. That said, it can also use indices on ids and the 2-character loc_code.

Solution 2 has no join, but makes the company table bigger and doesn't allow for an indexed search on loc_code.

Can anyone suggest other reasons to choose a solution?

There is also the choice of changing the location table to us the loc_code list and joining one-to-one with company.

Hope this makes sense. Thanks!

[mcibor]

As I recall the joins are much faster than like. However what you can also do is sth similar to your second solution. But instead of combining the country codes you can make a new record for each of them:

'company' (id, co_name, loc_code)
1, 'BT', 'BZ';
2, 'BT', 'CA';
3, 'BT', 'CY';
4, 'BT', 'DE';...

And the search
SELECT co_name FROM company WHERE loc_code ='BZ';

This way the search will be indexed, you won't have huge joins, only your table will be 4 times bigger.

I think this last solution is still the fastest, then with join and the slowest is with like.

Best regards
Michal Cibor

[dcrombie]

If you have a limited number of 'locations' a SET might be even faster. Or in postgres you could use an ARRAY data type.