MySQL Forums
Forum List  »  Newbie

Re: help understanding query...
Posted by: Jay Pipes
Date: July 15, 2005 01:27PM

Nathan Ziarek wrote:
> That does it!
>
> When you perform that join is it almost like (in
> terms of a typical table, like you'd see in excel)
> inserting a table into a cell?

Um, I guess. I don't really use Excel... :)

> I'm sorry, but the INNER JOIN is confusing, and
> the documentation here isn't very newbie
> friendly.

You'll get used to it, believe me. Just remember to think in terms of sets of data intersecting with each other. The inner join simply takes two sets of data and finds where they match on a certain field. You can repeat a join on a table's data numerous times, comparing values in diferent columns, the queries above demonstrate. Just go through the queries line by line until you understand how the sets of data are being joined (look in the ON clauses...) Eventually, it'll click.

> At the onset you mentioned that I didn't need the
> index. That is pretty much what I had figured,
> since I couldn't see a use for it. But...I guess I
> don't understand how having a PRIMARY KEY of (4,3)
> helps, though.

You don't need the auto_increment as a PRIMARY KEY because the imageid and keywordid columns form a natural unique key (primary key). Therefore, you can just set the PRIMARY KEY of the table to be these two columns.

> I'm probably not understanding. The
> end result of this will be an image browser that
> works by creating folders named by keyword. Each
> click will re run this query, perhaps several
> times, to determine what new folders should be
> displayed. The faster I can get it, the better,
> although it is pretty fast now :-)

Above is currently the fastest way I can think of to do this. MySQL will make use of the PRIMARY KEY index when performing the first join from m1 to m2 (because the imageid column is utilized first, then filtered by the keywordid). For the join from m2 to m3, the reverse index will be used, as the keywordid field only is used, and therefore the imageid column being on the left of the PRIMARY KEY index will prohibit MySQl from utilizing it for the join.

> Seems like the easiest
> part of learning something new is the actual
> learning; the hard part is trying to figure out
> what there is to learn. I've read a lot of your
> posts here in the newbie area, and I'm sure all
> the newbies appreciate the explanation you put
> into your answers.

You've got that right. We all learn something new every day. Cheers, and thanks for the kind words,

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Written By
Posted
Re: help understanding query...
July 15, 2005 01:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.