UBB.Developers Forums General Discussion Suggestions and Feedback LEFT OUTER JOINS IN 5.08

Scream,

I notice the increased use of LEFT (outer) joins in 5.08. While I appreciate the simplicity of db structure and coding this provides, I have always worked under the assumption that you take a performance hit when using these types of joins.

Because the query has to execute against both tables and you are not using a pre-built index, you are effectively performing two queries against the entire tables and a sort for every query that you submit (as I understand it - I could be wrong).

While it does not matter for small tables, and so should not matter for the category or boards tables, IMHO I think you should restrict LO joins to a minimum - and avoid them alltogther on the posts table.

I've actually seen a major application brought to it's knees through a single LEFT join.

Hope this is useful.

Dave.

This definitely won't be used any more than absolutely necessary. Up to this point I haven't used any JOINS at all and will continue to use them sparingly. The only reason they are used now is Simon Wolf came up with away of actually sorting the JUMP boxes properly, and using the JOINS was really the only way.

Dave,

I'm afraid I slightly disagree with your view. Using joins in a query, especially in mySQL which is 'join-friendly' (the mySQL site puts this as 'Very fast joins using an optimized one-sweep multi-join'), should actually decrease the server load. There is no necessity to run several queries separately and use code to match two or more tables' of data.

If joins were to be used extensively in WWWThreads then the database structure would need to be modified fairly extensively to improve table normalization. The benefits would be smaller databases (there are a lot of duplicate fields) and faster performance (less queries and code). On the down side, optimising the DB structure would involve a fairly massive re-write to WWWThreads which would take up a huge amount of Rick's time.

IMHO the major performance hit with WWWThreads is with the logging of what messages have been read. Viewing my forum as a guest the pages load quickly but when logged in they can crawl. A table-based solution may be better but it may slow things down massively.

Simon Wolf,
Webmaster,
Access All Areas
http://www.athree.com

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.html

While 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)

I'm not surprised you had problems with a 20GB database but I am guessing that most WWWThreads users only tens of thousands of posts rather than millions so the issue should not be too bad.

In addition, although the actual query may take longer to run, using joins can reduce the number of overall queries and 'in-between' code.

Finally, joins can make difficult tasks easy (which is why I used them for the forum drop-down list).

Your comments are all valid and true but not necesarliy (unless you are Sony) relevant to WWWThreads. If the joins are made on primary key fields which themselves are used as foreign keys in other tables then the performance impact for WWWThreads would be minimal. For example, an autonumber field in the users' table which is the primary key in the table could provide an efficient join to the posts table if the user is identified in that table by their unique ID.

Simon (Happy to debate these things)

Simon Wolf,
Webmaster,
Access All Areas
http://www.athree.com

Simon,

I'm happy to yield to your experience in this area - particularly as you have contributed much more to W3T that I probably ever will []/w3timages/icons/smile.gif[/].

However, one final observation. In the example you give of joining the user table to the posts table, the join you have shown is valid. However, the performance hit of doing the join the other way (from posts to users) could be much more significant - as a match has to be made from the users table against every row in the posts table.

Even with only "tens of thousands" of records (the 20GB system had less than 100,000 records in the tables in question), you need to watch those types of joins very carefully.

I guess my parting thought is that joins should be used where they make sense and do not impact performance - but that they should not be used just because it makes the programming easier - without thinking about what we are actually doing on the underlying database.

Dave.

As my final comment on this, the link from posts to users would indeed create a slightly more performance inensive query but the use of a primary key in the users table will dramatically reduce this ... The database 'knows' that the items are listed numerically (becuause it is the primary key) and therefore it 'knows' where to find the items.

Your database sounds impressive! The rows must have been contained massive amounts of data![]/w3timages/icons/smile.gif[/]

Joins generally don't make programming easier ... especially if they are used properly simply because the table design has to be normalized properly and because the queries themselves are more complex to design. Normalization is a major topic on its own and is the most significant cause of people having problems with relational databases.

Simon Wolf,
Webmaster,
Access All Areas
http://www.athree.com