SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2000
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Writing efficient mySQL Queries

    Hi,

    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.

  2. #2
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    These are not my words:

    A four table join will I guess have the habit of exploding rows^4 in time.

    doing a two table join twice will therefore only be 2*(rows^2) in time.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •