2 Qs: Multiple Queries vs. LEFT JOIN & Table Names
Hello all,
I am new to this forum, but have been using MySQL for a few years now. I try to increase my knowledge of it whenever I have the opportunity to try something new in a project. Recently I have been working on a website and came across a situation which I thought might warrant using a LEFT JOIN, but I am not sure of its efficiency compared to the way I'd normally do things.
Question 1: I have 3 tables: one for users and two others containing additional, optional information about each user. The user table has 3 fields, 1 that points to the first table and 2 that point to the second table. Not all users have a profile or addresses.
users
+-----+-----+------+------+--
| uid | pid | aid1 | aid2 | etc...
+-----+-----+------+------+--
profiles
+-----+------+--
| pid | name | etc...
+-----+------+--
addresses
+-----+--------+--
| aid | street | etc...
+-----+--------+--
At some point, I want to find all the information available about the user. Normally I would query for the user by his ID, then make additional queries based on the information found in his profile and address fields. This results in up to 3 queries. On the other hand, I can use 1 query with a big LEFT JOIN.
SELECT
u.*,
p.pid, p.name,
a1.aid AS aid1,
a2.aid AS aid2,
a1.street AS street1,
a2.street AS street2
FROM
users AS u LEFT JOIN profiles AS p ON u.profile=p.pid
LEFT JOIN addresses AS a1 ON u.aid1=a1.aid
LEFT JOIN addresses AS a2 ON u.aid2=a2.aid
WHERE
u.uid=[X]
This returns one result set with all the data I need. Now, I have read a bit about LEFT JOINs, but I don't know how efficient they are. Is the above statement better than doing 3 queries on tables with potentially thousands of entries? Why or why not? Is there a better way?
Question 2: My boss insists on the following naming convention:
Table Names: tableName
Field Names: tableName_fieldName
That seems redundant and very unnecessary to me, especially when I write queries which contain "tableName.tableName_fieldName". However, there may be some benefit of which I am unaware. Is there? What is a good naming convention?