Joins, for better and for worse

Recently I’ve been having one hell of a time with SQL Joins. It isn’t that I don’t understand how to use them, its that they have came to haunt me in completely opposite instances.

First: An application I had written a while ago that was suppose to be kept small, and very simple which continually was enlarged and became very big was running slow. I upgrade the person to our faster server believing it was just that our server that is meant for static sites couldn’t handle it. While it did greatly reduce the load time of the site(1/6) it was still slow for me. I looked into it and found that after all the alterations of the application which we had done, that one page had 1600 queries at its current state.

Before you think down upon our development abilities and our code efficiency let me put everything in perspective. The project was started as a very simple CRUD only database, that we did basically free for the a client who was a friend. Shortly after it continually was being expanded upon, and the friend could only afford minimal costs, so he asked us to just “hack” it together as it was a prototype. Well about a month later after development, we had possible one of the most hacked together prototypes for him. He never wanted to redo it from scratch so we could architect it correctly due to the cost. For the prototype, it worked fine and very fast. However the prototype turned into a beta test for the friend. He added much more information to the database, and the inefficient queries began to show. Now the application is loaded with information, and the inefficient queries are terrible. We have told him that if he plans to use this we should rewrite it from scratch as many other cool features could be used.

So back to the problem. Well we attempted to add JOINs to the program which should cut the number of queries by at least 1/10th. However when I added the left joins via Criteria/Propel I found that it was adding the joins twice. The reason they needed to be left joins is because the foreign keys were not required,