Why is making the LEFT JOIN ON(...AND...) this query slow?

Question

I can't figure out why this query is slow. It runs 3-4 seconds. When I remove the join on the wa_file_storage table the whole query runs in less than 0.02 seconds. explain doesn't show anything special, at least not for me. I have very likely a lack of knowledge to draw the right conclusion from the data shown in the explain. I guess it's something related to the two indexes used there and the key length?

What I figured out is that the combination of these two conditions is making it slow:

ON (
    `CompanyLogo`.`model` = 'CompanyLogo' 
    AND 
    `Companies`.`id` =     (`CompanyLogo`.`foreign_key`)
)

When I remove the CompanyLogo or the Companies condition from it the query runs fast again.

Why is this joins ON causing the query performance to become so bad and how can I fix this case?

Output of explain:

Output of explain with only the foreign_key field (runs 23sec!):

Output of explain with only the model field (runs 0.015sec):

The query:

SELECT `Jobs`.`id` AS `Jobs__id`,
       `Jobs`.`company_id` AS `Jobs__company_id`,
       `Jobs`.`job_category_id` AS `Jobs__job_category_id`,
       `Jobs`.`branch_id` AS `Jobs__branch_id`,
       `Jobs`.`title` AS `Jobs__title`,
       `Jobs`.`reference_number` AS `Jobs__reference_number`,
       `Jobs`.`location_same_as_office_address` AS `Jobs__location_same_as_office_address`,
       `Jobs`.`country_id` AS `Jobs__country_id`,
       `Jobs`.`city` AS `Jobs__city`,
       `Jobs`.`type_id` AS `Jobs__type_id`,
       `Jobs`.`description` AS `Jobs__description`,
       `Jobs`.`contact_first_name` AS `Jobs__contact_first_name`,
       `Jobs`.`contact_last_name` AS `Jobs__contact_last_name`,
       `Jobs`.`contact_tel` AS `Jobs__contact_tel`,
       `Jobs`.`contact_fax` AS `Jobs__contact_fax`,
       `Jobs`.`contact_email` AS `Jobs__contact_email`,
       `Jobs`.`show_job_on_world_architects` AS `Jobs__show_job_on_world_architects`,
       `Jobs`.`total_hits` AS `Jobs__total_hits`,
       `Jobs`.`last_activated_on` AS `Jobs__last_activated_on`,
       `Jobs`.`active` AS `Jobs__active`,
       `Jobs`.`slug` AS `Jobs__slug`,
       `Jobs`.`created` AS `Jobs__created`,
       `Jobs`.`modified` AS `Jobs__modified`,
       `Jobs`.`logo` AS `Jobs__logo`,
       `Jobs`.`activation_counter` AS `Jobs__activation_counter`,
       `Jobs`.`deactivated_by_client` AS `Jobs__deactivated_by_client`,
       `Jobs`.`logo_id` AS `Jobs__logo_id`,
       `Countries`.`id` AS `Countries__id`,
       `Countries`.`country` AS `Countries__country`,
       `Companies`.`id` AS `Companies__id`,
       `Companies`.`company` AS `Companies__company`,
       `Companies`.`company2` AS `Companies__company2`,
       `Companies`.`company3` AS `Companies__company3`,
       `Companies`.`street` AS `Companies__street`,
       `Companies`.`street2` AS `Companies__street2`,
       `Companies`.`pobox` AS `Companies__pobox`,
       `Companies`.`postal_code` AS `Companies__postal_code`,
       `Companies`.`city` AS `Companies__city`,
       `Companies`.`country_id` AS `Companies__country_id`,
       `Companies`.`state_id` AS `Companies__state_id`,
       `Companies`.`tel1` AS `Companies__tel1`,
       `Companies`.`tel2` AS `Companies__tel2`,
       `Companies`.`mobile` AS `Companies__mobile`,
       `Companies`.`url` AS `Companies__url`,
       `Companies`.`url2` AS `Companies__url2`,

       `CompanyLogo`.`id` AS `CompanyLogo__id`,
       `CompanyLogo`.`user_id` AS `CompanyLogo__user_id`,
       `CompanyLogo`.`foreign_key` AS `CompanyLogo__foreign_key`,
       `CompanyLogo`.`model` AS `CompanyLogo__model`,
       `CompanyLogo`.`filename` AS `CompanyLogo__filename`,
       `CompanyLogo`.`filesize` AS `CompanyLogo__filesize`,
       `CompanyLogo`.`mime_type` AS `CompanyLogo__mime_type`,
       `CompanyLogo`.`extension` AS `CompanyLogo__extension`,
       `CompanyLogo`.`hash` AS `CompanyLogo__hash`,
       `CompanyLogo`.`path` AS `CompanyLogo__path`,
       `CompanyLogo`.`adapter` AS `CompanyLogo__adapter`,
       `CompanyLogo`.`created` AS `CompanyLogo__created`,
       `CompanyLogo`.`modified` AS `CompanyLogo__modified`,

       `Profiles`.`id` AS `Profiles__id`,
       `Profiles`.`profile_category_id` AS `Profiles__profile_category_id`,
       `Profiles`.`status` AS `Profiles__status`,
       `Profiles`.`company_id` AS `Profiles__company_id`,
       `Profiles`.`profile_title` AS `Profiles__profile_title`,
       `Profiles`.`logo` AS `Profiles__logo`,
       `Profiles`.`established` AS `Profiles__established`,
       `Profiles`.`employee_number` AS `Profiles__employee_number`,
       `Profiles`.`slug` AS `Profiles__slug`,
       `Profiles`.`old_slug` AS `Profiles__old_slug`,
       `Profiles`.`domain_id` AS `Profiles__domain_id`,
       `Profiles`.`language_id` AS `Profiles__language_id`,
       `Profiles`.`keywords` AS `Profiles__keywords`,
       `Profiles`.`meta_description` AS `Profiles__meta_description`,
       `Profiles`.`created` AS `Profiles__created`,
       `Profiles`.`modified` AS `Profiles__modified`,
       `Profiles`.`hits` AS `Profiles__hits`,
       `Profiles`.`like_count` AS `Profiles__like_count`,
       `Profiles`.`social_buttons` AS `Profiles__social_buttons`,
       `Profiles`.`about` AS `Profiles__about`,
       `Profiles`.`team_description` AS `Profiles__team_description`,
       `Profiles`.`award_count` AS `Profiles__award_count`,
       `Profiles`.`project_count` AS `Profiles__project_count`,
       `Profiles`.`exhibition_count` AS `Profiles__exhibition_count`,
       `Profiles`.`employee_count` AS `Profiles__employee_count`,
       `Profiles`.`publication_count` AS `Profiles__publication_count`,
       `Profiles`.`competition_count` AS `Profiles__competition_count`,
       `Profiles`.`product_count` AS `Profiles__product_count`,
       `Profiles`.`facebook` AS `Profiles__facebook`,
       `Profiles`.`twitter` AS `Profiles__twitter`,
       `Profiles`.`is_setup` AS `Profiles__is_setup`,
       `CountryStates`.`id` AS `CountryStates__id`,
       `CountryStates`.`name` AS `CountryStates__name`
FROM `wa_jobs` `Jobs`
LEFT JOIN `wa_countries` `Countries` ON `Countries`.`id` = (`Jobs`.`country_id`)
LEFT JOIN `wa_companies` `Companies` ON `Companies`.`id` = (`Jobs`.`company_id`)
LEFT JOIN `wa_file_storage` `CompanyLogo` ON (`CompanyLogo`.`model` = 'CompanyLogo' AND `Companies`.`id` = (`CompanyLogo`.`foreign_key`))
LEFT JOIN `wa_profiles` `Profiles` ON `Companies`.`id` = (`Profiles`.`company_id`)
LEFT JOIN `wa_country_states` `CountryStates` ON `CountryStates`.`id` = (`Companies`.`state_id`)
ORDER BY `Jobs`.`id` DESC
LIMIT 50
OFFSET 850

The wa_file_storage schema:

CREATE TABLE `wa_file_storage` (
    `id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NULL DEFAULT NULL,
    `foreign_key` CHAR(36) NULL DEFAULT NULL,
    `model` VARCHAR(64) NULL DEFAULT NULL,
    `filename` VARCHAR(255) NOT NULL,
    `filesize` INT(16) NULL DEFAULT NULL,
    `mime_type` VARCHAR(32) NULL DEFAULT NULL,
    `extension` VARCHAR(5) NULL DEFAULT NULL,
    `hash` VARCHAR(64) NULL DEFAULT NULL,
    `path` VARCHAR(255) NOT NULL,
    `adapter` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Gaufrette Storage Adapter Class',
    `created` DATETIME NULL DEFAULT NULL,
    `modified` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `foreign_key_model` (`foreign_key`, `model`),
    INDEX `model` (`model`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

The wa_companies schema:

CREATE TABLE `wa_companies` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `company` VARCHAR(255) NOT NULL,
    `company2` VARCHAR(255) NOT NULL,
    `company3` VARCHAR(255) NOT NULL,
    `street` VARCHAR(255) NOT NULL,
    `street2` VARCHAR(255) NOT NULL,
    `pobox` VARCHAR(255) NOT NULL,
    `postal_code` VARCHAR(255) NULL DEFAULT NULL,
    `city` VARCHAR(255) NOT NULL,
    `country_id` INT(11) NULL DEFAULT NULL,
    `state_id` INT(11) NULL DEFAULT NULL,
    `tel1` VARCHAR(255) NOT NULL,
    `tel2` VARCHAR(255) NOT NULL,
    `mobile` VARCHAR(255) NOT NULL,
    `fax` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `url` VARCHAR(255) NOT NULL,
    `url_text` VARCHAR(255) NOT NULL,
    `url2` VARCHAR(255) NOT NULL,
    `url2_text` VARCHAR(255) NOT NULL,
    `headoffice_id` INT(11) NULL DEFAULT NULL,
    `selection_ranking` VARCHAR(255) NOT NULL,
    `positioning` VARCHAR(255) NOT NULL,
    `advisor_id` INT(11) NOT NULL,
    `status_id` INT(11) NOT NULL DEFAULT '1',
    `client_status_id` INT(11) NULL DEFAULT '17',
    `invoice_language_id` INT(11) NOT NULL DEFAULT '5',
    `total_credits` INT(11) NOT NULL,
    `tags` VARCHAR(255) NOT NULL,
    `uses_as_own_homepage` INT(11) NOT NULL DEFAULT '0',
    `address_checked` INT(11) NOT NULL DEFAULT '0',
    `approved` TINYINT(1) NOT NULL DEFAULT '0',
    `created` DATETIME NOT NULL,
    `modified` DATETIME NOT NULL,
    `role` VARCHAR(64) NOT NULL DEFAULT 'basic',
    `logo_image_id` VARCHAR(36) NULL DEFAULT NULL,
    `branch_count` INT(8) NOT NULL DEFAULT '0',
    `profile_constraint_id` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `country_id` (`country_id`),
    INDEX `state_id` (`state_id`),
    INDEX `headoffice_id` (`headoffice_id`),
    INDEX `status_id` (`status_id`),
    INDEX `client_status_id` (`client_status_id`),
    INDEX `PROFILE_CONSTRAINT_INDEX` (`profile_constraint_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=78372;

On IRC I was told to show the result of SHOW VARIABLES LIKE 'char%';:

The main reason for CHAR(36) is that we use UUIDs on newer tables. This DB is over 12 years old and involves ~250 tables, I'm there to get this mess fixed. :) I don't mind adding another table, but the main idea is that this table acts as reference point to all files no matter where they're stored.

Answer 1

The fact that you have an INT for wa_companies.id and a CHAR(36) for wa_file_storage.foreign_key is a part of the issue. MySQL has to convert between those datatypes to make comparisons, which will radically slow down joins.

While the displayed values will look the same to use humans, to a computer the values are not the same, and so must be converted before comparing each. The time it takes to make this comparison is small, but adds up very quickly when you consider it has to basically convert every row to verify if it matches or not. (Exact technical details may be off, but the overall gist and intuition is correct in terms of what happens to performance.)

The best way to structure the tables is to not use a single "mega" table, and instead create separate many to many tables with columns of minimal size in order to effectively join.

In addition, by creating a many to many table, you can join from that table to the wa_file_storage table via the wa_file_storage.id key. This will give you access to all the SELECTed columns that are present within the clustered index (aka PRIMARY KEY, at least for MySQL InnoDB tables). If MySQL ends up using an index, and then SELECT columns not in the index, it ends up having to make a second lookup to the clustered index, in order to obtain those values.

Keep in mind, JOINs are not slow. Poorly constructed JOINs that make the RDBMS do a lot of work are slow. Keep keys small and consistent in datatype, and join to primary keys (or covering indexes) as much as possible to keep things nice and easy for the RDBMS.