Selecting info from multiple mysql tables and display

[nomadhacker]

I have a web based program that I am trying to create, in which I will have a user create a profile, which optionally includes a homepage URL and/or blog URL. Since not every user will have a blog or a homepage to enter, I have moved that information to a separate table. However, when displaying the user profile page, I will obviously need to display both, and would like to use as few queries as possible to keep the speed snappy. My questions are: What syntax should I use for my multiple table query? Will a JOIN be the fastest? Or would a simple multi-table query work just as quickly? What is the difference between using INNER and LEFT Joins? And finally, when I access the information using php fetch_row functions, how do I designate which piece of data I am trying to access, since the syntax is $selectedrow and I am selecting from multiple tables, which may have some overlap of fields?

Any help would be appreciated.

So my table structure looks kind of like this:

usertable
	(
	userid		char(20)		NOT NULL	PRIMARY KEY
	userfirstname	char(50)	NOT NULL
	userlastname	char(50)	NOT NULL
	useremail	char(60)	NOT NULL	INDEX
	userrating	int	UNSIGNED	NOT NULL	
	userhandle	char(50)	NOT NULL	INDEX
	usersignupdate	datetime	NOT NULL	INDEX
	)

userhomepagetable
	(
	userid		char(20) 	UNSIGNED	NOT NULL	INDEX
	userhomepageurl	char(100)	NOT NULL
	userhomepagetitle	char(80)	NOT NULL
	)

userblogtable
	(
	userid		char(20)		UNSIGNED	NOT NULL	INDEX
	userblogurl	char(100)	NOT NULL
	userblogtitle	char(80)	NOT NULL
	)

[nav33n]

What syntax should I use for my multiple table query? Will a JOIN be the fastest? Or would a simple multi-table query work just as quickly?

I think sub-queries are much faster than joins and also easier to understand.

What is the difference between using INNER and LEFT Joins?

Check this site. Its too confusing(for me) to explain.

And finally, when I access the information using php fetch_row functions, how do I designate which piece of data I am trying to access, since the syntax is $selectedrow and I am selecting from multiple tables, which may have some overlap of fields?

For this, you can use the table name along with the column names and use an alias. Eg.

$query="select emp.name as empname, emp.no as empno, dept.name as deptname, dept.location as deptlocation from employee emp, department dept where emp.deptid=dept.deptid";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)){
   $employee_name=$row['empname'];
   $department_location=$row['deptlocation'];
   .......
}

I hope that helps! :)

[nomadhacker]

Thank you. That clears up how to reference the fields in php. I figure using aliases will help to make things much simpler.

I figured out the LEFT JOIN vs INNER JOIN. So it looks like I would need the LEFT JOIN since I want it to return all user information, not only if the second table fields exist.

I've never used subqueries. I looked up some information about them, and the syntax seems simple, but it looks like the LEFT JOIN may be faster.

Thank you again.

[iamthwee]

>I think sub-queries are much faster than joins and also easier to understand.

What do you mean by faster...

[nav33n]

A sub-query returns only those records which matches the condition where as joins returns the cartesian product(if no condition is specified).
If you want to fetch the data from many tables, sub-queries are more efficient than joins.
:)

[iamthwee]

So explain the following then:

In general, while the subquery is more easily understood and easier to formulate, the join actually improves performance because it gives the optimizer more efficient choices of how to execute the query.

http://docs.hp.com/en/36216-90103/ch03s02.html

Or are you talking about non-correlated sub queries. Please explain.

[nav33n]

In general, non-correlated subqueries are faster than joins, and correlated subqueries are slower than joins.

I was talking about non-correlated subqueries.

Ref: http://docs.hp.com/en/36216-90103/ch03s02.html

Oops! I didn't see your edited post !

[nomadhacker]

Thank you both for your reply. Now I have a better handle on joins and sub selects. However, do you think it would be faster or more efficient to simply include the homepage url and blog url in the main usertable and just allow null values?