Writing efficient mySQL Queries
I would welcome your thoughts and views as to what is more efficient (obviously this will depend on individual cases -but I'd like you 'general' views) - either using 1 large and complex query to join multiple tables or several small efficient join queries/non join queries.
As case point I have a database as follows : -
Table - "Companies" (each company can have mutiple "Locations") =>
Table - "Locations" (Each Location can have multiple "Activities")=>
Table - "Activities"
Now to search the database you can search either by "County" AND/OR "Activity_Category
Both of these (County and Activity_Category) are "pre-defined fields set in further tables which are linked to "Locations" and "Activity" by "Join" Tables
Hope your still with me!!
Now the result of any search must hold all the information from the Table "Companies", all the Locations that the relevant company has along with all the activities and relevant information on them.
So - I believe it is possible to retrieve all this information in one super complex Query with multiple joins - However the results array returned will contain massess of 'Repeated' information ie for every activity the company does you get repeated Company and Location information. This not only makes it a nightmare to filter everything into a non repeated format, but (I presume) uses up needless memory on repeated information.
Buy doing multiple queries I get round the problem of repeat information and I believe the information returned would be far easier to manage for output. However - by doing multiple queries am I not going to loose out on speed ?
I know this cannot be given a definative answer without looking at things far more indepth - however I'm new to mySQL and want to learn good practice from the start, so I'm looking for general thoughts and opinions on what is the best way to tackle situations like the above.
Look forwards to your thoughts.