[eluser]tonanbarbarian[/eluser]
first question: do you really need to get every single field from each table in the join?
removing fields that contain data you do not need would simplify things a bit.
secondly if you try to use a distinct in the query that may eliminate any rows that are exact duplicates
really need to know how much data is in each of the join tables and what it is all used for before you can come up with the best way to do things but there are some tips
It is usually better to get the data with 1 query rather than many if you can
However if your main table has lots of records (thousands) and your join tables contain very few records (hundreds or less) and always will contain few records then you can look at just grabbing the data from the main table and using queries to look up the extra information needed, such as the example linked in above.
However this method of running queries inside a loop has issues as well. The example has 6 queries inside a loop.
If the main loop is retrieving hundreds or thousands of rows then you will have 6 extra queries being run from the database for each row. This can result in hundreds of thousands of queries being run.
If you can limit the number of records you run in the main loop you can reduce the number of separate queries
Or you can prefetch join data.
So you run 6 queries before the loop and retrieve the data that would normally be retrieved in each of the 6 queries inside the loop. However these prefetches grab all of the data from the join tables rather than just those relating to a selected record. You then store this join data in their own separate array structures indexed by the values you would use inside the loop to run the queries.
Then inside the loop you lookup the values in the arrays rather than running separate queries.
This uses more memory but can reduce the number of queries from hundreds or thousands to just 7 in this case.
Ultimately you have to weight up all of the factors to determine whether to run a single joined query, a prefetched process, or the loop query process. They all work but each works best in different situations.
Always try to look ahead and estimate how much data could be in the live system in 1 year or even 5 years and let that guide you in the best way to do things.
