To JOIN or Rethink

Posted: Mon May 02, 2011 5:59 pm

Hey all-

I have 3 types of files I'm storing information on in the db in 3 separate tables (depending on what type of file it is) and I'm trying to teach myself mysql JOINS... however, I'm not sure this is the "best" approach.

First... would it be "wiser" to store all the files data in one table vs. three tables? (mind you... the data "could" become very big with time)

Depending on that answer... if JOINS are the way to go... most of the columns are identical between the tables with one exception ... one table has an added column.

I've tried to follow join tutorials on tizag.com & mysqljoin.com but they just confuse me more than anything.

If you're familiar with JOINS ... will you please break it down barney style for me. Thanks.

Posted: Tue May 03, 2011 3:48 pm

It really depends on what you are doing Smile

If you are using JOINs on primary keys, use InnoDB instead of MyISAM because it's quicker.
I really don't know enough about JOIN efficiency, whether it be an INNER or OUTER join, a JOIN with WHERE etc but I know some of the guys here DB guru's so I'm sure they will have some valuable input.

I would put my neck out and say that using one table would be more efficient than using three but that is provided you don't need to actually compare the data between the three tables or use the data in one table to determine what data you need to retrieve in the others.
I'm not explaining this very well so I'll try a different tack; if their is a 'many to many' relationship between the data in each of the tables, then use joins but since you have said that the tables are almost identical I would probably plump for one table and add an extra field to signify the file type. If you will only ever use those three file types, you could us an ENUM field to prevent anything other than those three files types from being added.

Posted: Tue May 03, 2011 3:55 pm

I agree with you outreached neck. The more I was thinking about it... it didn't make sense the way I initially designed the database. I went ahead and restructured it today and won't need joins until later in the project.

Thanks for the response. Wink

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Having been a DBA for much of my career (40 some years), I'm pretty familiar with joins and I try to avoid them whenever I can. Now understand that any time you link one table to another, whether using the JOIN keyword or not you are, in fact, joining them. But, it is a unique join as it only will produce matches comprise of records from both sets of data.
Now, if you want records only in one table or in neither, you get into JOINs.

That's a rather simplistic overview but that's what I intended.

Now as to using 1 or 3 tables you should really get an understanding of normalization to do it right. But, that's probably overkill for what you're designing Wink

. However, normalization is [basically] the practice of never repeating data in 2 or more tables other than the keys. So, you could have 1 table comprised of only unique keys and the other table has [possibly] multiple records with the same key but different data. So, rather than a join you would simply use a WHERE statement to the effect of WHERE table_1.key=table_2.key.

Hopefully this has helped. Also, I don't recommend using InnoDB unless you need transactional locking Wink

. The speed difference would really not be noticeable until you reached a very large database.

Posted: Fri May 06, 2011 7:08 am

Thank you for the clear explanation Raven!!! I'm no pro at database design but that is basically the jist I was getting from all the reading I did on normalization & joins.

The way I understood it at first was that separating data in smaller tables then using joins would provide better performance.... but as y'all have stated.... that is not really going to be the case.

I've now normalized it as best I can and will only use joins for one aspect of the project in which case I will use your example. (WHERE)

Thanks again guys!!

Posted: Tue Jul 05, 2011 1:59 pm

As a continuation to this discussion and what I've learned since...

Join tutorial for novices: (by real world example)

In working on my gallery module... I've come to a point in an area where I've deemed it worthy to use a join. Since I questioned how they work in the previous posts... I feel I should provide a working example for those who might come across this in the future and need some clarification by example.

In one of the process scripts... I need to gather information from the "album" table & "file" table.

The query:

Code:

$fileLocation = $db->sql_fetchrow($db->sql_query('SELECT at.album_Id, at.album_folder, ft.file_id, ft.file_title, ft.new_filename, ft.album_id 


FROM '.$prefix.'_swank_albums at 


JOIN '.$prefix.'_swank_files ft 


ON at.album_id = ft.album_id 


WHERE ft.file_id='.$fileID.' 


LIMIT 1')) or die(mysql_error());

So let's break this monster down a bit and see what's going on.

In the first part of the query... we see that we are assigning "$fileLocation" as the variable to hold the returned result. (yes... singular because I only want one result. Hence the "LIMIT 1")

Then we see:

Code:

$db->sql_fetchrow($db->sql_query(etc etc))

This is called a "Nested Query" where there is a query within a query.

And we arrive at the meat and potatoes of of the query:

Code:

'SELECT at.album_Id, at.album_folder, ft.file_id, ft.file_title, ft.new_filename, ft.album_id 


FROM '.$prefix.'_swank_albums at 


JOIN '.$prefix.'_swank_files ft 


ON at.album_id = ft.album_id 


WHERE ft.file_id='.$fileID.' 


LIMIT 1'

You may be wondering why there is an "at" or "ft" on the front of my column names. The reason for this is because in both tables... there is a column named "album_id". Since mysql wouldn't know which is which... I had to establish which table is which. "at = album table" , "ft = file table".

So we select the needed columns (prefixing them with the proper "at" or "ft" so we get the correct information from each table) FROM _swank_albums at (trailing "at" to establish the table) JOIN swank_files ft (trailing "ft" to establish the table) ON at.album_id = ft.album_id (album tables "album_id" equals files table "album_id") WHERE ft.file_id = $fileID (where the file table "file_id" equals the provided fileID) LIMIT 1 (to stop the query when the match is made)

I've also put in there:

Code:

or die(mysql_error())

This returns the error information so that if I've made a mistake in the coding or there is a error in the process... I'll receive the error response back from mysql.

Hopefully this helps someone in the future better understand how Joins work and how they can be used to reduce the code you must write to retrieve information from your database.

** Note **

This is my first attempt at joins and may or may not be the best practice. If you know of a better way or can explain it better... please post it so we can all benefit from your experiences.

Posted: Tue Jul 05, 2011 4:22 pm

Just one caveat Smile

. You should always use $user_prefix instead of $prefix, as they do not have to be the same. I know we still have legacy code that isn't 'correct' but at least new code should be Wink

.

Posted: Tue Jul 05, 2011 5:16 pm

You've lost me here.

From config.php: 2.40.01

Code:

// $prefix:           Your Database table prefix


// $user_prefix:      Your Users' Database table prefix (To share it)

I'm not pulling this information from the "users" table... so to pull "data" from the database... isn't "$prefix" correct? At the same time... is "$prefix" soon to be done away with in lieu of "$user_prefix"??

I guess what I'm getting at in a more direct way is this... if I run a fresh install... and I leave $prefix = 'nuke' & $user_prefix = 'test'... when the database is built... which of the two will it prefix the database with??

Edit****

RN Wiki wrote:

$user_prefix
This should ALWAYS be the same as the $prefix setting unless you are serving up more than one nuke site from the same database. This allows you to use certain tables across several nuke sites. This is an advanced feature and not one to take lightly, so know what you are doing if you change this.

What are those "certain tables"? If this is nothing more than the "users" tables (i.e. login information) then $prefix is correct since I'm not pulling users information... but rather stored data... correct?

Posted: Wed Jul 06, 2011 1:56 am

You would use $user_prefix for any/all tables you wanted to share. Presently I think we only use $user_prefix for the users and temp_users tables. It's no 'big' deal Wink

.

Posted: Wed Jul 06, 2011 9:58 am

Hmmm... I've never given thought to sharing the data from the gallery module across multiple sites. They way I have it visioned... the gallery would be associated with one site.

Now that you've brought this up... do you see scenarios where the gallery would/should be used across multiple sites? (please let me know your opinion... changing $prefix => $user_prefix is an easy change if it is necessary)

I think I'm grasping what your intentions are though... to cover that "what if" in the event the user tries to share the data from the gallery.

Posted: Wed Jul 06, 2011 12:18 pm

I think Raven was just making a generalization not really talking about your specific instance, but that is just a guess Wink

Posted: Wed Jul 06, 2011 12:32 pm

That's kinda what I'm gathering... but it raises an interesting thing for me to consider in the process.

Since $user_prefix "should" be the same as $prefix... but "could" be different... would it be better to write code using "$user_prefix" to head off potential problems in the future? This doesn't apply just to this instance only, but rather as a coding practice in general just to ensure that I cover all the bases in that "what if" instance.

I'm assuming that's the point he's making but I just want to be crystal clear on it so I can go ahead and switch my mindset.

Posted: Wed Jul 06, 2011 8:23 pm

Compared to any other module I do not see why you would need to share this across multiple sites.

Posted: Thu Jul 07, 2011 12:14 am

Palbin wrote:

I think Raven was just making a generalization not really talking about your specific instance, but that is just a guess Wink

Correct.

Posted: Thu Jul 07, 2011 7:08 am

Thanks guys! Learn something new everyday.