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
FORCE INDEX
(it looks like client_code is a better index)? Why are you usingDISTINCT
, a) its grouped by that, b) its contracts.id the PK? IncludeSHOW CREATE TABLE contacts
andSHOW CREATE TABLE emails_stats
. What example results are you after?SHOW CREATE TABLE contacts|emails_stats
. I'm usingDISTINCT
to get different values fromcontacts.email
column. Yes, thecontacts.id
is the primary key.GROUP BY contacts.id
the output will be a unique contract.id soDISTINCT
is not needed and created ambiguities as to what you want. Show example results (asking for second time).SELECT id FROM contacts WHERE client_code = 121212121212
and creating an index on client codeCREATE INDEX client_code ON contacts(client_code)
.EXPLAIN
. Look to the query, i addedcontacts.email
andemails_stats.id
select