MySQL Single Left Join Very Slow
Because you are grouping by contracts.email, there needs to be an aggregate expression over emails_stats.id, which I've chosen as GROUP_CONCAT:

SELECT
    contacts.email, GROUP_CONCAT(emails_stats.id) FROM contacts
LEFT JOIN
    emails_stats ON contacts.id = emails_stats.contact_id
WHERE
    contacts.client_code = 121212121212
GROUP BY
    contacts.email;


As this lookup is by client_code, to enable this to perform, creating an index on client_code needs to be at the first part of the index. As contacts.email is used by the GROUP BY and SELECT results, putting email at the end of the index will save a secondary lookup of the email, and being in the index, its already sorted and hence grouped together.

CREATE INDEX client_code_email ON contacts(client_code,email)


This will enable the WHERE clause to be fast. In Innodb which is used, the contacts.id is the PK at the end of the key, this is use the JOIN which is ideal.

The JOIN to email_stats is on contract_id which already has an index. email_stats.id is the PK already at the end of this index which gets used in the result set.