unexpected/not understood join behavior
Posted: Mon Mar 29, 2004 9:20 am
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 datathis is what the call from the script getshere's what i want to have retrieved (note this was not actually retrieved, but created by me to show what i want)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)
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)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)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)