[MySQL] Trage LEFT JOIN combined PRIMARY KEY

[Tripy]

Voor een export van klantprofielen heb ik een gecombineerde primary key op de tabel: copernica_profiles
PRIMARY KEY is `customer_email` en `id_shop`

De relatie met de tabel: ps_customer ligt op email adres omdat er meerdere klanten voorkomen met hetzelfde email adres.

In de tabel ps_customer voor id_shop = 4 heb ik ongeveer 100.000 records
In de tabel copernica_profiles heb ik ongeveer 80.000 records
Van ongeveer 20.000 records zijn er dus nog geen copernica profielen.

Doormiddel van een LEFT JOIN wil ik dus ook resultaten terugkrijgen van klanten die nog geen profiel hebben.
Het kan echter zijn dat hetzelfde email adres in verschillende shops terugkomt, dus heb ik een conditie op id_shop = 4 in de LEFT JOIN naar de tabel copernica_profiles nodig.

Deze query doet er gemiddeld 5 seconden over:

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.customer_email = customer.email AND profiles.id_shop = 4)
WHERE customer.id_customer > 0
AND   customer.active      = 1
AND   customer.id_shop     = 4
LIMIT 100

Deze query doet er gemiddeld 35 ms over:

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.customer_email = customer.email)
WHERE customer.id_customer > 0
AND   customer.active      = 1
AND   customer.id_shop     = 4
AND   profiles.id_shop     = 4
LIMIT 100

De laatste query geeft alleen records terug die zowel in ps_customer als in copernica_profiles terugkomen.
Echter wil ik ook records hebben die NIET in copernica_profiles voorkomen.
Gezien de eerste query voor 100 records er 5 seconden over doet, zal het proces voor 100.000 records 5.000 seconden nodig hebben.

Iemand ideeën hoe ik toch relatief snel de gewenste resultaten kan behalen?

[Feanathiel]

Zou je van beide situaties een 'explain' kunnen uitvoeren en hier neer zetten? Wellicht kunnen we hier wat additionele informatie uit destilleren welke keuzes de engine neemt.

[Tripy]

Query 1:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	customer	ref	PRIMARY,id_customer_passwd,id_shop	id_shop	4	const	61851	Using where
1	SIMPLE	profiles	index	NULL	id_profile	4	NULL	118174	Using index

Query 2:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	profiles	index	NULL	id_profile	4	NULL	118174	Using where; Using index
1	SIMPLE	customer	ref	PRIMARY,customer_email,customer_login,id_customer_passwd,id_shop	customer_email	386	func	1	Using where

[pedorus]

Die doortrekking lijkt me onlogisch, tenzij het een erg beroerde database server is (met weinig geheugen). Ik vermoed dat je wat indexes mist, maar desnoods is 100.000 records gewoon niet zo heel veel dus is het een kwestie van afwachten totdat het resultaat er is.

Overigens kun je ook een constructie als (AND (profiles.customer_email is null OR profiles.id_shop = 4) gebruiken.

Na de explain: Het lijkt erop dat je een gecombineerde index op (id_profileid_shop, customer_email) mist, waardoor die opgebouwd moet worden bij de eerste query. En bij customer (id_shop,customer_email).

[ Voor 3% gewijzigd door pedorus op 06-11-2014 21:08 ]

[Tripy]

Zojuist een gecombineerde index toegevoegd op id_profile en customer_email, maar maakt op de 1e query niets uit qua snelheid.
Nieuwe explain:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	customer	ref	PRIMARY,id_customer_passwd,id_shop	id_shop	4	const	61851	Using where
1	SIMPLE	profiles	index	NULL	id_profile	4	NULL	106842	Using index

Deze query is zelfs nog een seconde trager dan de eerste.

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.customer_email = customer.email)
WHERE customer.id_customer > 0
AND   customer.active      = 1
AND   customer.id_shop     = 4
AND  (profiles.id_shop     = 4 OR profiles.id_shop is null)
LIMIT 100

[Speedener]

Wat doet een gecombineerde index op de profiles tabel: customer_email + id_shop ?

En dan gewoon je eerste query uit je TS doen.

[pedorus]

Als je zo snel een index op kan bouwen, dan zou die totale query ook niet heel lang duren (als het een eenmalig iets is).

Maar vergeet die bij customer niet die ik erbij geedit had: customer (id_shop,customer_email).

[Feanathiel]

Hmm. `customer` is goedkoper om te scannen volgens de statistieken, dus die zal hij als eerste doen. Wat doet de non-const join?

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.customer_email = customer.email AND profiles.id_shop = customer.id_shop)
WHERE customer.id_customer > 0
AND   customer.active      = 1
AND   customer.id_shop     = 4
LIMIT 100

[ Voor 3% gewijzigd door Feanathiel op 06-11-2014 17:49 ]

[Tripy]

INDEX toegevoegd op ps_customer voor de kolommen id_shop en email.
Eerste Query draait nog net zo traag.

Deze Query is ook net zo traag.

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.customer_email = customer.email AND profiles.id_shop = customer.id_shop)
WHERE customer.id_customer > 0
AND   customer.active      = 1
AND   customer.id_shop     = 4
LIMIT 100

[Tripy]

Ben de volledige query lokaal aan het draaien op SSD en 2.2Ghz CPU (zonder limit en alle benodigde velden)
Staat nu al 5100 seconden te draaien en is nog niet klaar...

[Tripy]

Lokaal 100% CPU utilization en 180MB memory met 20 threads

[pedorus]

f.kwakkenbos schreef op donderdag 06 november 2014 @ 17:48:
INDEX toegevoegd op ps_customer voor de kolommen id_shop en email.

In welke volgorde heb je deze index gemaakt? Heb je nu een key op (id_shop,email) bij beiden?

En ik zou het ietsje herschrijven zodat de volgorde logisch is voor die keys:

SQL:

LEFT JOIN `copernica_profiles` AS profiles ON 
    (customer.id_shop = profiles.id_shop AND customer.email = profiles.customer_email)
WHERE customer.id_shop = 4 AND customer.active = 1 AND customer.id_customer > 0

180 MB is verder vrij weinig geheugen. En wat geeft een explain nu?

[Tripy]

De INDEX op ps_customer:
ALTER TABLE `ps_customer` ADD INDEX (`id_shop`, `email`);

Query zoals die nu is:

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.id_shop = customer.id_shop AND profiles.customer_email = customer.email)
WHERE customer.id_shop     = 4
AND   customer.active      = 1
AND   customer.id_customer > 0
LIMIT 100

Taking 5,81 seconds

EXPLAIN:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	customer	ref	PRIMARY,id_customer_passwd,id_shop,id_shop_2	id_shop	4	const	61533	Using where
1	SIMPLE	profiles	index	NULL	id_profile	4	NULL	106870	Using index

Ik heb nog wat geprobeerd.
Een kleine variatie op Query #2 die er 35ms over deed, maar dan dus zonder de conditie op profiles.id_shop = 4

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.customer_email = customer.email)
WHERE customer.id_shop     = 4
AND   customer.active      = 1
AND   customer.id_customer > 0
LIMIT 100

Taking 5,01 seconds

EXPLAIN:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	customer	ref	PRIMARY,id_customer_passwd,id_shop,id_shop_2	id_shop	4	const	61533	Using where
1	SIMPLE	profiles	index	NULL	id_profile	4	NULL	106871	Using index

Nogmaals Query #2:

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.customer_email = customer.email)
WHERE customer.id_shop     = 4
AND   customer.active      = 1
AND   customer.id_customer > 0
AND   profiles.id_shop = 4
LIMIT 100

Taking 35 ms

EXPLAIN:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	profiles	index	NULL	id_profile	4	NULL	106871	Using where; Using index
1	SIMPLE	customer	ref	PRIMARY,customer_email,customer_login,id_customer_passwd,id_shop,id_shop_2	customer_email	386	func	1	Using where

[ Voor 51% gewijzigd door Tripy op 06-11-2014 19:54 ]

[pedorus]

Mwja, het valt me op dat possible_keys bij profiles NULL is, en niet de naam van een key op (`id_shop`, `customer_email`) bevat. Voor een snelle join is op beide tabellen een bruikbare key nodig, idealiter clustered maar dat hoeft niet perse.

Een extra losse key op id_shop bij ps_customer is niet echt nodig als je een gecombineerde key hebt die daarmee begint.

Anders moet je even de output van show create table laten zien.

[Tripy]

CREATE TABLE `copernica_profiles` (
  `customer_email` varchar(128) NOT NULL,
  `id_profile` int(11) unsigned NOT NULL,
  `id_shop` int(11) NOT NULL DEFAULT '4',
  PRIMARY KEY (`customer_email`,`id_shop`),
  KEY `id_profile` (`id_profile`),
  KEY `customer_email` (`customer_email`,`id_profile`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `ps_customer` (
  `id_customer` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_shop_group` int(11) unsigned NOT NULL DEFAULT '1',
  `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
  `id_gender` int(10) unsigned NOT NULL,
  `id_default_group` int(10) unsigned NOT NULL DEFAULT '1',
  `id_lang` int(10) unsigned DEFAULT NULL,
  `id_risk` int(10) unsigned NOT NULL DEFAULT '1',
  `company` varchar(64) DEFAULT NULL,
  `siret` varchar(14) DEFAULT NULL,
  `ape` varchar(5) DEFAULT NULL,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `email` varchar(128) NOT NULL,
  `passwd` varchar(32) NOT NULL,
  `last_passwd_gen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `birthday` date DEFAULT NULL,
  `newsletter` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ip_registration_newsletter` varchar(15) DEFAULT NULL,
  `newsletter_date_add` datetime DEFAULT NULL,
  `optin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `website` varchar(128) DEFAULT NULL,
  `outstanding_allow_amount` decimal(10,6) NOT NULL DEFAULT '0.000000',
  `show_public_prices` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `max_payment_days` int(10) unsigned NOT NULL DEFAULT '60',
  `secure_key` varchar(32) NOT NULL DEFAULT '-1',
  `note` text,
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_guest` tinyint(1) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  PRIMARY KEY (`id_customer`),
  KEY `customer_email` (`email`),
  KEY `customer_login` (`email`,`passwd`),
  KEY `id_customer_passwd` (`id_customer`,`passwd`),
  KEY `id_gender` (`id_gender`),
  KEY `id_shop_group` (`id_shop_group`),
  KEY `id_shop` (`id_shop`),
  KEY `id_shop_2` (`id_shop`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=485753 DEFAULT CHARSET=utf8

[pedorus]

copernica_profiles bevat dus geen key op (`id_shop`, `customer_email`) maar een primary key op (`customer_email`,`id_shop`) die helaas niet bruikbaar is omdat de volgorde omgedraait is. Ik zou denken verander de volgorde van de primary key in je favoriete tool, of voeg de omgekeerde index ook toe.

Beware: http://stackoverflow.com/...pdating-mysql-primary-key

En de key id_shop (zonder 2) kan denk ik wel weg bij ps_customer.

(Oh, en ik zie dat mijn eerste post een tikfoutje bevatte..... )

[ Voor 8% gewijzigd door pedorus op 06-11-2014 21:17 . Reden: +linkje ]

[Tripy]

Dat wordt dan:
ALTER TABLE `copernica_profiles` DROP PRIMARY KEY;
ALTER TABLE `copernica_profiles` ADD PRIMARY KEY (`id_shop`, `customer_email`);

Betreft ps_customer
Stel dat ik die KEY `id_shop` (`id_shop`) weg zou halen en ik JOIN alleen op die `id_shop`, gebruikt die dan gewoon die combined INDEX op KEY `id_shop_2` (`id_shop`,`email`) ?

[naam]

Wat mogelijk ook een probleem oplevert is dat de ps_customer tabel in utf8 is, en de copernica_profiles tabel in Latin1, al weet ik dit niet zeker.

[ Voor 14% gewijzigd door naam op 06-11-2014 21:52 ]

[pedorus]

Good catch, dat lijkt me ook niet zo handig. Ik heb het nooit uitgeprobeert, maar het lijkt mij dat mysql dan een conversie moet doen of zelfs de key niet kan gebruiken. Ik zou die profiles tabel omzetten naar utf8, ook omdat dit voor emailadressen toch niets uitmaakt.

Over gecombineerde keys: je kunt het gewoon vergelijken met een telefoonboek. Een telefoonboek gesorteerd op naam en daarna op nummer is prima bruikbaar om op naam te zoeken, het scheelt alleen maar als er meerdere telefoonnummers zijn met dezelfde naam. Enkel die sortering is dan weer niet erg bruikbaar om op nummer te zoeken, omdat je dan alsnog alle namen afmoet.

Voor joins is een index met meer keys erna dus wel bruikbaar, een index met minder of een verkeerde volgorde niet.

[Tripy]

Hey! Dat was het!

SQL:

RENAME TABLE `copernica_profiles` TO `copernica_profiles_original`;

SQL:

CREATE TABLE `copernica_profiles` (
  `customer_email` varchar(128) NOT NULL,
  `id_profile` int(11) unsigned NOT NULL,
  `id_shop` int(11) NOT NULL DEFAULT '4',
  PRIMARY KEY (`id_shop`,`customer_email`),
  KEY `id_profile` (`id_profile`),
  KEY `customer_email` (`customer_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL:

INSERT INTO `copernica_profiles`
SELECT 
    `customer_email`,
    `id_profile`,
    `id_shop`
FROM `copernica_profiles_original`;

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.id_shop = 4 AND profiles.customer_email = customer.email)
WHERE customer.id_shop     = 4
AND   customer.active      = 1
AND   customer.id_customer > 0
AND  profiles.id_profile is null
LIMIT 100

Taking 123ms

EXPLAIN:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	customer	ref	PRIMARY,id_customer_passwd,id_shop	id_shop	4	const	62432	Using where
1	SIMPLE	profiles	eq_ref	PRIMARY,customer_email	PRIMARY	390	const,brekz_2013.customer.email	1	Using where; Not exists

SQL:

SELECT
  profiles.id_profile,
  customer.active,
  customer.email,
  customer.id_customer
FROM `ps_customer`             AS customer
LEFT JOIN `copernica_profiles` AS profiles ON (profiles.id_shop = 4 AND profiles.customer_email = customer.email)
WHERE customer.id_shop     = 4
AND   customer.active      = 1
AND   customer.id_customer > 0
LIMIT 100

Taking 2ms

@pedorus Thanks voor je hulp!
@naam Jij ook uiteraard...

[ Voor 13% gewijzigd door Tripy op 06-11-2014 22:30 ]

[GlowMouse]

pedorus schreef op donderdag 06 november 2014 @ 21:10:
copernica_profiles bevat dus geen key op (`id_shop`, `customer_email`) maar een primary key op (`customer_email`,`id_shop`) die helaas niet bruikbaar is omdat de volgorde omgedraait is.

Het juiste woord is 'omgedraaid', en de volgorde is in dit geval onbelangrijk omdat op beide velden wordt geselecteerd. Bovendien zal zoeken op e-mailadres al selectief genoeg zijn om de dataset tot een paar rijen te beperken, dus een index op customer_email zou ook al voldoende zijn.

Tripy schreef op donderdag 06 november 2014 @ 21:46:
Betreft ps_customer
Stel dat ik die KEY `id_shop` (`id_shop`) weg zou halen en ik JOIN alleen op die `id_shop`, gebruikt die dan gewoon die combined INDEX op KEY `id_shop_2` (`id_shop`,`email`) ?

Ja, je kunt in de customer-tabel de indexe customer_login, id_customer_passwd en id_shop_2 zonder problemen weghalen, aangezien er geen queries gedraaid zullen worden die voordeel hebben bij deze indices.