ansaurus

Question

Should I store country names in mysql for each user?

Answer 1

A:

Usually, comparing integer values is more efficient than comparing strings.

zipcodeman 2010-01-09 03:02:10

Answer 2

+3 A:

It would be fastest to keep a table called countries and a field on your users table called country or country_id that holds the foreign key for each users country in the countries table.

A join in MYSQL is normally (almost always) faster than iterating through a list of returned records and pairing the country with the user.

Doug Neiner 2010-01-09 03:02:59

This would not only be the fastest, but also the best solution. The number of countries in our world is fixed, and usually it stays as this ;)

frunsi 2010-01-09 03:08:54

Actually it'd be pretty fast to avoid the join altogether and just dump the strings in. I agree with your approach, but it's not the fastest (at least for non-country-dependent-record-retrieval).

Mark E 2010-01-09 03:14:33

you could also then store the db data in something like memcache and it would pull from a warm cache (ram) instead of doing another query

Tom Schlick 2010-01-09 03:15:53

@Mark, true. But if if he didn't need the country for a specific query, he shouldn't run the join. @trs21219 Yes. If he were using Rails, I would say use the local hash. Rails loads it once upon startup and keeps it in memory if you set it up right.

Doug Neiner 2010-01-09 03:21:43

@Doug, the point is that the straight retrieval case "get me all users and their countries" costs a join in this solution. The only savings of using numbers would be "get me all users from country X" where the number X is known a priori (if it's not you suffer the join *and* the string compare)

Mark E 2010-01-09 03:24:46

@Mark again, excellent points. But the string compare will only be run against a total of 228 items. In a straight string solution with `20,000` users, you would be comparing against 19772 more strings than you need to. Providing the join is written correctly :)

Doug Neiner 2010-01-09 03:28:04

Answer 3

A:

It seems to me that putting the names of the countries in the table with the usernames will save you memory performance as you won't be storing the names of the countries in an array. But you won't save much else as you're making the same number of queries as you are with the original way except without the query to get the country names.

indyK1ng 2010-01-09 03:03:26

Answer 4

A:

I'm not very familiar with PHP, but couldn't you store the country list in the DB (with a FK in your users table to this list), store the list in the cache, and update the cache whenever something changes? It seems like with all the instability in the world, you'd like to be able to update this list somewhat frequently, and hard coding it like this will require you to change your code every time there's a civil war.

ristonj 2010-01-09 03:05:26

Answer 5

A:

For initial load of the data, you could use the ISO-3166 data. Most Linux distros come with a package (iso-codes) containing a list of all the countries and their names translated to many languages. In several formats too (CSV, XML, and .po for the translations).

http://pkg-isocodes.alioth.debian.org/

Nicolás 2010-01-09 03:12:32

Answer 6

A:

The keys should be integers, not strings, other than that it's fine.

gdonald 2010-01-09 03:16:54

Answer 7

+2 A:

Edit: on second pass, consider all of what I said below about strings, but opt for an enum field. Create an enum in the database containing all countries, and use that rather than strings or another table. It should give you all of the benefits of using strings (like getting the value you want back without a join) and all of the benefits of using a second table.

Assuming that your primary use case is something to the effect of "get me all users and country" or "get me user N and country" then the fastest you can do is to store the string.

This has some pitfalls: string compares are more costly than say an indexed approach (discussed in another answer), and if you plan on often running something to the effect of "get me all users from country X" where you know the index well then you can avoid the string compare.

Even if the latter case exists, it really only pays to have another table if the latter case dominates the usage, the data is going to be changing (you plan on adding new countries later, updating the names of the countries), if you want to change the language of the name of the country to change based on some user locale, etc.

Avoiding joins will save you some execution time, but a second table could also be right for you.

Mark E 2010-01-09 03:29:06

+1 Great detail, great answer -- I still think normalization wins out, but I see lots of room for this working as well.

Doug Neiner 2010-01-09 03:38:58

Doug, I don't disagree, but having written my share of databases for user profiles I can't recall the last time I wanted to get users by country =). Perhaps an enum would be the most efficient solution?

Mark E 2010-01-09 03:40:52

LOL... you are right. I am discussing in theory. It is much different in practice.

Doug Neiner 2010-01-09 03:50:53

I didn't even know you could do an enum with that many possible values! I usually do an enum with like 3-4 values, doing one with 200+ won't hurt performance?

jasondavis 2010-01-09 04:03:16

jasondavis, I can't say I have any experience with enums this large, but they amount to restricted string sets, so my expectation is that the performance is better than the join alternative in the 90% case and comparable (or better) when you're using it as a query input.

Mark E 2010-01-09 04:18:42

Answer 8

+2 A:

To improve speed create a table with MEMORY storage engine , once the database starts fill the table with your values,it is extra fast.

Also access to your array is very fast, because you are not searching the array, you know the position (with int keys) and just seek there.

amir beygi 2010-01-09 05:37:03

ansaurus

tags:

views:

answers:

Should I store country names in mysql for each user?

related questions