Is this MySQL query the best idea?

Question

Hi guys. The project I'm working on has two type of accounts, "people" and "companies". I hold a single "users" table with all the accounts and just the basic info needed for login (email, pass, etc), and two other tables "user_profiles" (regular people) and "company_profiles" (companies) that hold more specific columns for each type, both of the tables linked to the general "users" table via a "profile_user_id" column.

Now, the problem: whenever I want to list users that can be both people and companies, I use a: "select user_id, user_type, concat_ws('', concat_ws(' ', user_profiles.profile_first_name, user_profiles.profile_last_name), company_profiles.profile_company_name) as user_fullname ". When I list these users I know whether they're people or companies by the "user_type".

Is my approach using concat_ws the right (optimal) one? I did this instead of select-ing every *_name to avoid returning more columns than necessary.

Any protips are very welcome :).

Thanks

EDIT: the query above continues like: from users left join user_profiles on ... left join company_profiles on ...

Answer 1

select u.user_id, u.user_type, concat_ws(profile_first_name + profile_last_name) as full_name from users u, user_profiles up where u.key = up.key and u.user_type = 'user'

union

select u.user_id, u.user_type, concat_ws(profile_first_name + profile_last_name) as full_name from users u, company_profiles cp where u.key = cp.key and u.user_type = 'company'

Answer 2

Does the query you already have work? Is it that you're experiencing performance issues from using this approach already?

Unless using the above query is taking way longer than you're expecting it to be or is causing issues in the software calling this information, this might be pre-mature optimization.

One thing to note though, your first use of CONCAT_WS doesn't have a seperator, so the company name is going to be merged with the person's name.