Referencing a username

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[Nox]

I think numerical id would be faster - shorter, more easily sortable etc., not to mentioned never changed, easily used manually, occupies much less space in the end 'cause of references...

[Chris]

Never use varchar in MyISAM (except for messages). It makes the whole table much slower. You would be better to even split the table in two if it would remove the need for varchar in the most used part of the table.

As for username vs id+JOIN, it depends how often the table is accessed (for buddy list I would probably go for id+JOIN). Generally, avoiding JOIN is faster (exceptions), the drawback is bloated database and more complex code (breakage of normalisation has some drawbacks, althrough it is not as huge cost as most people think).

As for selecting, then obviously id is far faster than any string. So storing username to use it as an identifier when you could use id is just insane

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[133794m3r]

wait now i saw that you shouldn't use it for MyISAM abut what about INNODB should you use something like char(20)? instead of varchar(20) b/c i read in that tips thread that the peformance is lightly greater. So how much of a difference is the performance between teh two?

[Marek]

A numerical id will still be faster than a char, if I'm not mistaken.

For the original question, I think the best balance of simplicity vs performance is using an int id and a join for the username. This is the "conventional" way to structure a relation of this sort. Don't optimize prematurely: start with the conventional approach, and then if you see that in practice, the query's performance is a bottleneck, then consider optimizing it.

The reason why optimizing prematurely can be bad is that often, the real bottlenecks are in different spots than you first think. So it's best to have concrete evidence in the form of performance profiling, and only then optimizing, starting with what can be improved the most.

[JGadrow]

nano's suggestions above are my advice as well. Well done, nano!

There is no need to copy the username to various tables and, indeed, numbers are always faster than characters. Remember, everything with computers eventually ends up as 1s and 0s. The fewer 1s and 0s you end up with, the better! lol