Wolf,
No need to be afraid, active discussion is what makes this forum and product so great []/w3timages/icons/smile.gif[/]. Also, I don't want to labor over this point, as Rick has already said that they won't be used extensively. However, I wanted to respond to why I think joins are a bad idea...
As I understand the functionality of the
left outer join process, it combines all the items in one table with information from only the rows in the other table which meet the matching condition. This involves 3 actions:
1. A selection againt all the rows of the original table
2. A selection against the matching rows of the 2nd table
3. The creation of a temporary recordset to hold the results
There is a good article on this at
http://mysql.net/Manual_chapter/manual_Performance.htmlWhile LOJ's are good for small tables, for very large tables they involve a trememdous amount of overhead. In those cases, I have generally found it more preferable to add a separate table that maps the relationship. This way, when executed it involves a query against a much smaller table that does not have to be generated. It de-normalizes the database further, but generally improves performance.
Now, I should add that I'm not a database expert - I've just had the bad experience of having a large (20GB) database application brought to it's knees by the poor use of LOJ's. IMVHO, they should be reserved for tables which contain a limited number of rows.
However, I would also agree with you that there are lots of other areas in W3T which could benefit from performance tuning - I just wanted to make certain that Rick understood the potential downsides of LOJ's so as not to create further problems.
Dave (Happy to be shown the error of my ways)
