0
SELECT
    contacts.email, 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;

This query taken 10.2s

Table contacts:

Rows: ~74k; Indexes: primary and client_code

Table emails_stats

Rows: ~20k; Indexes: primary and contact_id

The query explain:

1   SIMPLE  contacts    ref client_code client_code 9   const   2104    Using temporary; Using filesort
1   SIMPLE  emails_stats    index   NULL    contact_id  36  NULL    19394   Using where; Using index; Using join buffer (Block Nested Loop)

SHOW CREATE TABLE contacts:

CREATE TABLE `contacts` (
  `id` char(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `client_code` bigint(20) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW CREATE TABLE emails_stats:

CREATE TABLE `emails_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_event_id` int(11) NOT NULL,
  `contact_id` char(36) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `email_event_id` (`email_event_id`),
  KEY `contact_id` (`contact_id`),
) ENGINE=InnoDB AUTO_INCREMENT=19194 DEFAULT CHARSET=latin1

Expected Result:

email                       id
[email protected]    136
[email protected]    365
[email protected]    765
7
  • You aren't including emails_stats in any critiera so why are you joining by them? What happens with explain if you don't FORCE INDEX (it looks like client_code is a better index)? Why are you using DISTINCT, a) its grouped by that, b) its contracts.id the PK? Include SHOW CREATE TABLE contacts and SHOW CREATE TABLE emails_stats. What example results are you after?
    – danblack
    Mar 27, 2019 at 23:49
  • Hey! Thanks for the comment. I updated the question adding the SHOW CREATE TABLE contacts|emails_stats. I'm using DISTINCT to get different values from contacts.email column. Yes, the contacts.id is the primary key.
    – Kafer76
    Mar 28, 2019 at 0:05
  • What contracts.email? It isn't in the query. You have presented a broken query with no description of what example results you are after. It can't be answered in the current form. When you GROUP BY contacts.id the output will be a unique contract.id so DISTINCT is not needed and created ambiguities as to what you want. Show example results (asking for second time).
    – danblack
    Mar 28, 2019 at 0:37
  • In its current form the ideal simplification yielding identical results and performance improvement of this question is SELECT id FROM contacts WHERE client_code = 121212121212 and creating an index on client code CREATE INDEX client_code ON contacts(client_code).
    – danblack
    Mar 28, 2019 at 2:23
  • Thanks for comment. I added expected result and changed de EXPLAIN. Look to the query, i added contacts.email and emails_stats.id select
    – Kafer76
    Mar 28, 2019 at 16:30

3 Answers 3

2

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.

4
  • nice suggestion! you are absolutely right - in the original form of query 2nd table don't change anything! :-)
    – a_vlad
    Mar 28, 2019 at 11:28
  • I added the emails_stats to appear in the SELECT result set. Look the new modifications in the question, i added the expected result. Thanks for the comment
    – Kafer76
    Mar 28, 2019 at 16:31
  • @ypercubeᵀ The second table is needed because I want a specific column from there
    – Kafer76
    Mar 28, 2019 at 16:48
  • 1
    Totally written answer based on totally different question. Please aim for complete and correct questions showing edge cases next time @Kafer76
    – danblack
    Mar 29, 2019 at 0:36
1

UPD - there is no reason to edit the answer, after changing the question, logic is changed for 100% as well and no reasons to repeat the @danblack's answer.

The only one is possible to add - the more correct and full the question, the more fast and correct answers!


  1. as mentioned in comments - you do not need to include DISTINCT in the query code, you already have GROUP BY
  2. you also do not need FORCE INDEX
  3. but you have not index for client_code, and this is the most important part - this is only one that could reduce the number of scanned rows, without this index mysql always will make the full table scan and only then sort filtered rows

suggestions:

  • exclude from code DISTINCT and FORCE INDEX
  • add index on table contacts for client_code or possible (client_code, id)
6
  • Good summary. Note that MySQL PK is always implicitly at the end of secondary keys so client_code is sufficient.
    – danblack
    Mar 28, 2019 at 2:25
  • I would, on the contrary, recommend removing GROUP BY and leaving DISTINCT. On the modelling dataset (1kk records, 32k distinct values) the execution time is ~7% less.
    – Akina
    Mar 28, 2019 at 4:59
  • @danblack "..PK is always implicitly at the end of secondary keys.." For InnoDB engine only.
    – Kondybas
    Mar 28, 2019 at 9:57
  • @Akina - as a variant. I personally prefer GROUP BY - just because in most of the case some other columns there - max, avg and etc
    – a_vlad
    Mar 28, 2019 at 11:26
  • In the contacts table, client_code and id column are indexes. Look the new modifications in the question above. Thanks for the comment!
    – Kafer76
    Mar 28, 2019 at 16:32
0

UUIDs are terrible for performance when the table becomes bigger than can be cached in RAM. Did this happen?

How much RAM? What is the value of innodb_buffer_pool_size?

Add this covering, composite index:

INDEX(client_code, email, id) -- in this order

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.