MySQL Forums
Forum List  »  General

Re: Optimising query with many joins
Posted by: Peter Brawley
Date: March 31, 2017 09:07AM

I added BBCode code tags to your 2nd post to make it readable.

The query formatted for readability:

SELECT 
  *, pictures.id AS picture_id, 
  aircraft.id AS aircraft_id, 
  aircraft.verified AS aircraft_verified, 
  aircraft.type AS aircraft_type, 
  operators.verified AS operator_verified, 
  airports.verified AS location_verified, 
  displayteams.verified AS displayteam_verified, 
  airshows.verified AS airshow_verified, 
  picture_categories.verified AS picturecategory_verified, 
  CONCAT(aircraft.type, " ", aircraft.model) AS aircraft_type_search 
FROM `pictures` 
LEFT JOIN `aircraft`           ON `aircraft`.`id` = `pictures`.`aircraft_id` 
LEFT JOIN `operators`          ON `operators`.`id` = `pictures`.`operator_id` 
LEFT JOIN `airports`           ON `airports`.`id` = `pictures`.`location_id` 
LEFT JOIN `operator_category`  ON `operator_category`.`id` = `pictures`.`operator_category_id` 
LEFT JOIN `displayteams`       ON `displayteams`.`id` = `pictures`.`displayteam_id` 
LEFT JOIN `airshows`           ON `airshows`.`id` = `pictures`.`airshow_id` 
LEFT JOIN `picture_categories` ON `picture_categories`.`id` = `pictures`.`picture_category_id` 
WHERE `pictures`.`display_picture` = 'yes' 
ORDER BY `date_taken_formatted` ASC 
LIMIT 30;

SELECT * is a bad idea.

Left Joins don't optimise well, usually the engine has to do a table scan on the left-sided table.

The joins look to be following a straightforward data model. Why not inner joins?

For this query, each table should have an index on the column used for it in its Join.

The `display_picture` column is yes/no? That's uselessly minimal selectivity on 225k rows.

If these fixes don't make performance adequate, let's see SHOW CREATE TABLE results for the referenced tables, and the relevant my.cnf settings.

Options: ReplyQuote


Subject
Written By
Posted
Re: Optimising query with many joins
March 31, 2017 09:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.