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

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,
Job

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.

StackExchange Database Administrators Q#172530, answer score: 5