MySQL Query is far too complicated

Question

Hi there, I've got a query that I've made but I've got a problem with -

Is this the 'neatest' way of writing this? I'm this could be written a lot better, I've heard you can 'loop through recordset' using more queries - would this be a good thing to do? And if so can someone point me in the right direction? This query must use up a lot of memory due to it being spread acrosss 8 tables including a sub-query with 3 tables.

Note - I'm using a slave database with read-only access so changing structure/creating tables etc. is impossible!

Many thanks for any help. Very much appreciated.

Select travel_booking.reference As REF,
  travel_group.name As `Group Name`,
  site_user.firstname As `First Name`,
  site_user.lastname As `Last Name`,
  site_user.gender,
  travel_package.start_date As `Start Date`,
  travel_booking_option.name As `Option Name`,
  travel_booking_option.text_box_text As `Free Text`,
  travel_booking_option.select_box_option As `Select Text`,
  travel_booking_option.option_price As `Option Price`,
  content.name As `Option Type`
  From travel_booking Inner Join
  travel_booking_option On travel_booking_option.travel_bookingid =
  travel_booking.travel_bookingid Inner Join
  travel_group On travel_group.travel_groupid = travel_booking.travel_groupid
Inner Join
  site_user On site_user.site_userid = travel_booking.site_userid Inner Join
  travel_option_group On travel_option_group.travel_option_groupid =
  travel_booking_option.travel_option_groupid Inner Join
  content On travel_option_group.travel_option_type_content_realid =
  content.content_realid Inner Join
  travel_package On travel_package.content_realid =
  travel_booking.travel_packageid

 Where travel_booking_option.fee = 0 And travel_booking_option.refund = 0 And
  travel_booking_option.cancel = 0 And travel_booking.cancel = 0 And
  travel_package.live = 1 And content.live = 1

Group By travel_booking.reference,
  travel_group.name,
  site_user.firstname,
  site_user.lastname,
  site_user.gender,
  travel_package.start_date,
  travel_booking_option.name,
  travel_booking_option.text_box_text,
  travel_booking_option.select_box_option,
  travel_booking_option.option_price,
  content.name

Answer 1

You would have to review the execution plan of the query to see if it is inefficient or not. As long as the proper indices are used while execution, you should be fine.

You ask about "'loop through recordset' using more queries". That is a RBAR (row-by-agonizing-row) approach and is most definitely not recommended.

I would reformat the query to make it more readable as follows:

Select 
    travel_booking.reference As REF,
    travel_group.name As `Group Name`,
    site_user.firstname As `First Name`,
    site_user.lastname As `Last Name`,
    site_user.gender,
    travel_package.start_date As `Start Date`,
    travel_booking_option.name As `Option Name`,
    travel_booking_option.text_box_text As `Free Text`,
    travel_booking_option.select_box_option As `Select Text`,
    travel_booking_option.option_price As `Option Price`,
    content.name As `Option Type`

From travel_booking 
    Inner Join travel_booking_option 
        On travel_booking_option.travel_bookingid = travel_booking.travel_bookingid 
    Inner Join travel_group 
        On travel_group.travel_groupid = travel_booking.travel_groupid
    Inner Join site_user 
        On site_user.site_userid = travel_booking.site_userid 
    Inner Join travel_option_group 
        On travel_option_group.travel_option_groupid = travel_booking_option.travel_option_groupid 
    Inner Join content 
        On travel_option_group.travel_option_type_content_realid = content.content_realid 
    Inner Join travel_package 
        On travel_package.content_realid = travel_booking.travel_packageid

Where 1=1
    And travel_booking_option.fee = 0 
    And travel_booking_option.refund = 0 
    And travel_booking_option.cancel = 0 
    And travel_booking.cancel = 0 
    And travel_package.live = 1 
    And content.live = 1

Group By 
    travel_booking.reference,
    travel_group.name,
    site_user.firstname,
    site_user.lastname,
    site_user.gender,
    travel_package.start_date,
    travel_booking_option.name,
    travel_booking_option.text_box_text,
    travel_booking_option.select_box_option,
    travel_booking_option.option_price,
    content.name

I would also use aliases for table names.

Answer 2

It looks to me like you're GROUPing BY every column in your result set but not aggregating any columns. If that's so, you can simplify by removing the GROUP BY at the end and instead inserting the word DISTINCT after SELECT.

You could also shorten (but not actually simplify) the query by using table aliases. For instance, if you change FROM travel_booking to FROM travel_booking TB you could then use TB. in place of travel_booking. throughout the query. Similarly with other tables (use a different alias for each table, of course).

Finally, if your application is not going to use the column aliases to build the UI automatically (eg, for column headings in a grid) you could get rid of the column aliases as well.

Otherwise the query looks pretty straightforward. People sometimes worry that something's wrong when they have several JOINs in their query but, in fact, it's more likely to be a sign that the database is well structured.

My version of the SQL (column aliases included):

SELECT DISTINCT TB.reference As REF, TG.name As `Group Name`,
  SU.firstname As `First Name`, SU.lastname As `Last Name`, SU.gender,
  TP.start_date As `Start Date`,
  TBO.name As `Option Name`, TBO.text_box_text As `Free Text`, TBO.select_box_option As `Select Text`, TBO.option_price As `Option Price`,
  C.name As `Option Type`
 FROM travel_booking TB 
    Inner Join travel_booking_option TBO  On TBO.travel_bookingid = TB.travel_bookingid
    Inner Join travel_group TG On TG.travel_groupid = TB.travel_groupid
    Inner Join site_user SU On SU.site_userid = TB.site_userid 
    Inner Join travel_option_group TOG On TOG.travel_option_groupid = TBO.travel_option_groupid
    Inner Join content C On TOG.travel_option_type_content_realid = C.content_realid
    Inner Join travel_package TP On TP.content_realid = TB.travel_packageid
 WHERE TBO.fee = 0 And TBO.refund = 0 And TBO.cancel = 0 
    And TB.cancel = 0 And TP.live = 1 And C.live = 1