Page 1 of 1

unexpected/not understood join behavior

Posted: Mon Mar 29, 2004 9:20 am
by m3rajk
i completely redid my forum script using the old one as a shell in order to cut down the file size and speed up the script.
the file size went from approx 33000 to approx 22000. the speed is much faster since i removed a lof of if/elseif lines and if they were necessary used a switch instead. the file size really shrunk from adding modularity that was really needed.

i also modified the database structure to use joins (natural joins mostly). however the joins are giving the information i want and i'm not sure why. i'm wondering if i can get explanations on why so that i can fix this (and possibly advice too since i'm sure there's a good number of people with more expereince than i that read these boards)

the following is an example from the database, what i got when i tried to get the main forum page. this is what i did to verify the existance of the data

Code: Select all

mysql> select tid, fid, sub from threads;
+-----+-----+---------------------------+
| tid | fid | sub                       |
+-----+-----+---------------------------+
|   1 |   1 | point of this forum       |
|   2 |   2 | Interests                 |
|   3 |   3 | about calling others fake |
|   4 |   3 | Saluting                  |
|   5 |   4 | Hey!                      |
|   6 |   5 | EVERYONE                  |
|   7 |   2 | testing guest posting     |
|   9 |   2 | logging in                |
|  10 |   8 | what not to wear on TLC   |
|  11 |   4 | Yo                        |
|  12 |   4 | Just testing...           |
|  13 |   1 | Test                      |
|  14 |   4 | Mistakes and Errors       |
|  15 |   4 | dev to demo to....        |
|  16 |   2 | test                      |
|  17 |   4 | double checking           |
+-----+-----+---------------------------+
16 rows in set (0.05 sec)
 
mysql> select fid, fal, title from forums;
+-----+-----+-------------------------+
| fid | fal | title                   |
+-----+-----+-------------------------+
|   1 |   5 | Mod Review              |
|   2 |   0 | Problems/Suggestions    |
|   3 |   0 | Fakes                   |
|   4 |   1 | General/Misc            |
|   5 |   1 | Shouts                  |
|   6 |   1 | Arts & Crafts       |
|   7 |   1 | Entertainment           |
|   8 |   1 | Fashion & Style     |
|   9 |   1 | Rants & Raves       |
|  10 |   1 | Relationships           |
|  11 |   1 | Technology              |
|  12 |   1 | Vehicles                |
|  13 |   3 | Adult Misc              |
|  14 |   3 | Erotica                 |
|  15 |   3 | Sex & Adult Chat    |
|  16 |   2 | Desired's Play Pen |
|  17 |   4 | Approve & Moderate  |
+-----+-----+-------------------------+
17 rows in set (0.00 sec)
this is what the call from the script gets

Code: Select all

mysql> select tid, threads.fid, fal, sub, title from threads natural join forums;
+-----+-----+-----+-------------------------+----------------------+
| tid | fid | fal | sub                     | title                |
+-----+-----+-----+-------------------------+----------------------+
|   4 |   3 |   0 | Saluting                | Fakes                |
|   6 |   5 |   1 | EVERYONE                | Shouts               |
|  10 |   8 |   1 | what not to wear on TLC | Fashion & Style  |
|  13 |   1 |   5 | Test                    | Mod Review           |
|  16 |   2 |   0 | test                    | Problems/Suggestions |
|  17 |   4 |   1 | double checking         | General/Misc         |
+-----+-----+-----+-------------------------+----------------------+
6 rows in set (0.00 sec)
here's what i want to have retrieved (note this was not actually retrieved, but created by me to show what i want)

Code: Select all

mysql> select tid, threads.fid, fal, sub, title from threads natural join forums;
+-----+-----+-----+---------------------------+----------------------+
| tid | fid | fal | sub                       | title                |
+-----+-----+-----+---------------------------+----------------------+
|   1 |   1 |   5 | point of this forum       | Mod Review           |
|   2 |   2 |   0 | Interests                 | Problems/Suggestions |
|   3 |   3 |   0 | about calling others fake | Fakes                |
|   4 |   3 |   0 | Saluting                  | Fakes                |
|   5 |   4 |   1 | Hey!                      | General/Misc         |
|   6 |   5 |   1 | EVERYONE                  | Shouts               |
|   7 |   2 |   0 | testing guest posting     | Problems/Suggestions |
|   9 |   2 |   0 | logging in                | Problems/Suggestions |
|  10 |   8 |   1 | what not to wear on TLC   | Fashion & Style  |
|  11 |   4 |   1 | Yo                        | General/Misc         |
|  12 |   4 |   1 | Just testing...           | General/Misc         |
|  13 |   1 |   5 | Test                      | Mod Review           |
|  14 |   4 |   1 | Mistakes and Errors       | General/Misc         |
|  15 |   4 |   1 | dev to demo to....        | General/Misc         |
|  16 |   2 |   0 | test                      | Problems/Suggestions |
|  17 |   4 |   1 | double checking           | General/Misc         |
+-----+-----+-----+---------------------------+----------------------+
17 rows in set (0.00 sec)
if you would like, i can post the file and table descriptions. right now i only modified the development server. the table was modified to remove some extra database calls from the original script and some extra instances of usernames (trying to remove even more after this to add a new function for the higher user level)

Posted: Mon Mar 29, 2004 10:03 am
by redmonkey
It has been a while since I have used a basic join but from what I remember (and I could be wrong) that that type of join uses the primary key for it's join condition. So I'm guessing but it does look like you are joining on the fid.

Have you tried....

Code: Select all

select threads.tid, threads.fid, forums.fal, threads.sub, forums.title from threads, forums where (threads.fid = forums.fid)
or...

Code: Select all

select t.tid, t.fid, f.fal, t.sub, f.title from threads t left join forums f on (t.fid = f.fid)

Posted: Thu Sep 16, 2004 3:59 pm
by Weirdan
MySQL manual wrote: The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.
Are you sure your tables does not share any column names except fid? (you haven't posted all of your columns, thus the question).

Posted: Thu Sep 16, 2004 4:04 pm
by Weirdan
Weirdan wrote:
MySQL manual wrote: The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.
Are you sure your tables does not share any column names except fid? (you haven't posted all of your columns, thus the question).
Edit: Oops, I was thinking it was 'New posts since last visit' search :lol: