Slome query tussen twee databases

[PaulEm]

Tot op heden heeft de onderstaande query normaal gefunctioneerd, echter neemt deze nu bijna zo'n 3 minuten in.

SQL:

SELECT d.*,
       a.*
FROM   icecat_data d
       LEFT JOIN artikeldata.artikels a
              ON Trim(a.a_reflev) = d.product_id
WHERE  d.data IS NOT NULL
       AND d.invalid = 0
       AND d.pending = 1
ORDER  BY a.a_artstat DESC

Trim op a_reflev is nodig omdat de data die in deze tabel gedumped wordt standaard een X-aantal spaties bevat. Deze data is niet in eigen beheer dus valt niet op te lossen. Wanneer ik de tabel tijdelijk een update geef met trim(a_reflev) en deze vervolgens achterwege laat in de query maakt dit verder geen performance verschil.

Een explain geeft mij het volgende terug:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	d	ALL	NULL	NULL	NULL	NULL	1400	Using where; Using temporary; Using filesort
1	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	17415

Dit is de structuur van de tabellen:

SQL:

CREATE TABLE IF NOT EXISTS `icecat_data` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `date_created` datetime NOT NULL,
  `date_updated` datetime NOT NULL,
  `product_id` varchar(30) NOT NULL,
  `vendor` varchar(25) NOT NULL,
  `data` mediumtext,
  `pending` tinyint(1) NOT NULL DEFAULT '1',
  `invalid` tinyint(1) NOT NULL DEFAULT '0',
  `removed` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_id` (`product_id`,`vendor`),
  KEY `product_id_2` (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

SQL:

CREATE TABLE IF NOT EXISTS `artikels` (
  `a_stuknr` varchar(30) NOT NULL DEFAULT '0',
  `a_sorteer` float(8,3) NOT NULL,
  `a_lveld1` tinyint(1) NOT NULL DEFAULT '0',
  `e_lveld1` tinyint(1) NOT NULL DEFAULT '0',
  `a_lveld2` tinyint(1) NOT NULL DEFAULT '0',
  `a_lveld3` tinyint(1) NOT NULL DEFAULT '0',
  `a_lveld4` tinyint(1) NOT NULL DEFAULT '0',
  `a_type` varchar(20) NOT NULL DEFAULT '',
  `a_volgnr` varchar(20) NOT NULL DEFAULT '0',
  `a_nummer` varchar(20) NOT NULL DEFAULT '',
  `a_reflev` varchar(50) NOT NULL DEFAULT '',
  `a_folder` tinyint(1) NOT NULL DEFAULT '0',
  `a_fprijsd` float(8,3) NOT NULL DEFAULT '0.000',
  `a_fprijsm` float(8,3) NOT NULL DEFAULT '0.000',
  `a_fdatum` date NOT NULL DEFAULT '0000-00-00',
  `a_fvdatum` date NOT NULL DEFAULT '0000-00-00',
  `a_fprijsi` float(8,3) NOT NULL DEFAULT '0.000',
  `a_groep` char(3) NOT NULL DEFAULT '',
  `a_merkid` smallint(3) NOT NULL DEFAULT '0',
  `a_minverk` float(8,3) NOT NULL DEFAULT '0.000',
  `a_omschrn` varchar(250) NOT NULL DEFAULT '',
  `a_omschrf` varchar(200) NOT NULL DEFAULT '',
  `recupeurex` float(8,3) NOT NULL DEFAULT '0.000',
  `a_soort` char(3) NOT NULL DEFAULT '',
  `a_artstat` char(1) NOT NULL DEFAULT '',
  `a_instock` smallint(5) NOT NULL DEFAULT '0',
  `a_bokl` tinyint(5) NOT NULL DEFAULT '0',
  `a_bolv` tinyint(5) NOT NULL DEFAULT '0',
  `a_verdat` date NOT NULL DEFAULT '0000-00-00',
  `e_instock` smallint(5) NOT NULL,
  `e_bokl` tinyint(5) NOT NULL,
  `e_bolv` tinyint(5) NOT NULL,
  `g_bokl` tinyint(5) NOT NULL,
  `g_bolv` tinyint(5) NOT NULL,
  `g_verdat` date NOT NULL,
  `l_instock` smallint(5) NOT NULL,
  `l_bokl` tinyint(5) NOT NULL,
  `l_bolv` tinyint(5) NOT NULL,
  `l_verdat` date NOT NULL,
  `m_instock` smallint(5) NOT NULL,
  `m_bokl` tinyint(5) NOT NULL,
  `m_bolv` tinyint(5) NOT NULL,
  `m_verdat` date NOT NULL,
  `d_instock` smallint(5) NOT NULL,
  `d_bokl` tinyint(5) NOT NULL,
  `d_bolv` tinyint(5) NOT NULL,
  `d_verdat` date NOT NULL,
  `s_instock` smallint(5) NOT NULL,
  `s_bokl` tinyint(5) NOT NULL,
  `s_bolv` tinyint(5) NOT NULL,
  `s_verdat` date NOT NULL,
  `u_instock` smallint(5) NOT NULL,
  `u_bokl` tinyint(5) NOT NULL,
  `u_bolv` tinyint(5) NOT NULL,
  `u_verdat` date NOT NULL,
  `a_nomstock` smallint(5) NOT NULL DEFAULT '0',
  `a_aanbevol` float(8,3) NOT NULL DEFAULT '0.000',
  `a_basisex` float(8,3) NOT NULL DEFAULT '0.000',
  `a_dealer` float(8,3) NOT NULL DEFAULT '0.000',
  `a_winkel` float(8,3) NOT NULL DEFAULT '0.000',
  `a_winex` float(8,3) NOT NULL DEFAULT '0.000',
  `a_fprijs` float(8,3) NOT NULL DEFAULT '0.000',
  `a_btwcode` smallint(5) NOT NULL DEFAULT '0',
  `e_verdat` date NOT NULL,
  `g_instock` smallint(5) NOT NULL,
  `a_cveld6` char(20) DEFAULT NULL,
  `last_update_stock` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `last_update_article` datetime DEFAULT NULL,
  `Reprobelex` float(8,3) DEFAULT NULL,
  `Auvibelex` float(8,3) DEFAULT NULL,
  `Bebatex` float(8,3) DEFAULT NULL,
  `recupelex` float(8,3) DEFAULT NULL,
  `b_instock` smallint(5) NOT NULL,
  `b_bokl` tinyint(5) NOT NULL,
  `b_bolv` tinyint(5) NOT NULL,
  `b_verdat` date NOT NULL,
  `c_instock` smallint(5) NOT NULL,
  `c_bokl` tinyint(5) NOT NULL,
  `c_bolv` tinyint(5) NOT NULL,
  `c_verdat` date NOT NULL,
  `f_instock` smallint(5) NOT NULL,
  `f_bokl` tinyint(5) NOT NULL,
  `f_bolv` tinyint(5) NOT NULL,
  `f_verdat` date NOT NULL,
  `h_instock` smallint(5) NOT NULL,
  `h_bokl` tinyint(5) NOT NULL,
  `h_bolv` tinyint(5) NOT NULL,
  `h_verdat` date NOT NULL,
  PRIMARY KEY (`a_nummer`),
  KEY `a_groep` (`a_groep`),
  KEY `a_merkid` (`a_merkid`),
  KEY `a_soort` (`a_soort`),
  KEY `a_volgnr` (`a_volgnr`),
  KEY `a_reflev` (`a_reflev`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Indexes zijn toegevoegd aan a_reflev en product_id. De data verwerken vanuit twee aparte queries wordt vrijwel onmogelijk, PHP heeft het namelijk best zwaar om 17k rijen van artikeldata te verwerken. Heeft iemand nog tips om deze query te optimaliseren?

[Woy]

Ik heb je post niet echt goed gelezen, dus heb geen idee wat het probleem exact is, maar het eerste wat me opvalt is de gekke join die je hebt

SQL:

ON Trim(a.a_reflev) = d.product_id 

Waarom doe je een join op een ID met een waarde die je moet TRIMMEN? Dat lijkt me verdacht.

Verder is het natuurlijk ook een kwestie van de juiste indexen toevoegen, want die zie ik nu niet terug komen op de velden waarvoor dat logisch zou zijn.
Niet goed gekeken. Maar ik neem aan dat de index niet gebruikt wordt door de TRIM

[ Voor 30% gewijzigd door Woy op 29-06-2012 10:17 ]

[TheNephilim]

Woy schreef op vrijdag 29 juni 2012 @ 10:15:
Ik heb je post niet echt goed gelezen, dus heb geen idee wat het probleem exact is, maar het eerste wat me opvalt is de gekke join die je hebt

SQL:

ON Trim(a.a_reflev) = d.product_id 

Waarom doe je een join op een ID met een waarde die je moet TRIMMEN? Dat lijkt me verdacht.

Verder is het natuurlijk ook een kwestie van de juiste indexen toevoegen, want die zie ik nu niet terug komen op de velden waarvoor dat logisch zou zijn.
Niet goed gekeken. Maar ik neem aan dat de index niet gebruikt wordt door de TRIM

`a_reflev` varchar(50) NOT NULL DEFAULT '',

Volgens mij moet je die even casten naar INT in je JOIN. Misschien gaat het dan goed?

[ Voor 4% gewijzigd door TheNephilim op 29-06-2012 10:19 ]

[CodeCaster]

PaulEm schreef op vrijdag 29 juni 2012 @ 10:02:
Tot op heden heeft de onderstaande query normaal gefunctioneerd, echter neemt deze nu bijna zo'n 3 minuten in.

Ik ken geen bug die specifiek vandaag getriggerd wordt in MySQL, dus wellicht is er aan de situatie iets veranderd? Heb je ineens veel meer data, is er veel meer verkeer over het netwerk?

Verder is je datamodel verre van optimaal, maar daar zul je op dit moment weinig aan kunnen doen. Lees eens iets over normalisatie.

[Devil]

Kun je geen trigger maken die a.a_reflev automatisch trimmed op insert?
Verder een beetje experimenteren met het maken van indexes. Hou er rekening mee dat er voor elke component die je bij de 'explain' ziet maar 1 index gebruikt kan worden. Vooral op tabel a kun je waarschijnlijk flink wat performance winst halen met bijvoorbeel een key op revlev en artstat.

Ik zou daar een beetje mee experimenteren.

[PaulEm]

Woy schreef op vrijdag 29 juni 2012 @ 10:15:
Ik heb je post niet echt goed gelezen, dus heb geen idee wat het probleem exact is, maar het eerste wat me opvalt is de gekke join die je hebt

SQL:

ON Trim(a.a_reflev) = d.product_id 

Waarom doe je een join op een ID met een waarde die je moet TRIMMEN? Dat lijkt me verdacht.

Verder is het natuurlijk ook een kwestie van de juiste indexen toevoegen, want die zie ik nu niet terug komen op de velden waarvoor dat logisch zou zijn.

Zoals vermeld staat in mijn startpost, deze waarde moet een TRIM over omdat de data die aangeleverd wordt spaties bevat (deze data is buiten ons beheer om). Voor welke velden zou het volgens jou logisch zijn om indexen op toe te voegen? a_reflev en product_id bevatten beiden namelijk een index op dit moment. Op a_artstat zit verder geen index, maar wanneer ik deze ORDER weg laat geeft dit verder geen performance verschil.

TheNephilim schreef op vrijdag 29 juni 2012 @ 10:18:
[...]


`a_reflev` varchar(50) NOT NULL DEFAULT '',

Volgens mij moet je die even typecasten naar INT in je JOIN. Misschien gaat het dan goed?

Typecasten gaat moeilijk, deze waarde bestaat namelijk niet alleen uit een INT.

CodeCaster schreef op vrijdag 29 juni 2012 @ 10:19:
[...]

Ik ken geen bug die specifiek vandaag getriggerd wordt in MySQL, dus wellicht is er aan de situatie iets veranderd? Heb je ineens veel meer data, is er veel meer verkeer over het netwerk?

Verder is je datamodel verre van optimaal, maar daar zul je op dit moment weinig aan kunnen doen. Lees eens iets over normalisatie.

Klopt, het probleem is dat de "artikels" tabel buiten ons beheer valt. Deze tabel wordt zo gevuld door voorraad/kassasysteem.

Devil schreef op vrijdag 29 juni 2012 @ 10:21:
Kun je geen trigger maken die a.a_reflev automatisch trimmed op insert?
Verder een beetje experimenteren met het maken van indexes. Hou er rekening mee dat er voor elke component die je bij de 'explain' ziet maar 1 index gebruikt kan worden. Vooral op tabel a kun je waarschijnlijk flink wat performance winst halen met bijvoorbeel een key op revlev en artstat.

Ik zou daar een beetje mee experimenteren.

Op reflev zit al een index en als ik a_artstat weg laat maakt dit verder geen performance verschil helaas.

[_JGC_]

Ik zie 3 mogelijke performanceproblemen:
- TRIM in je JOIN, maar volgens jou maakt dit niks uit
- Je joint InnoDB aan MyISAM. Grote kans dat als je je MyISAM tabel ook InnoDB maakt dat je, mits je server getuned is voor InnoDB, een stuk minder performanceproblemen hebt
- Je charset voor de ene tabel is UTF8, voor de andere Latin1. Dit zorgt voor extra onnodige conversies. Maak die twee gelijk, iig op het veld dat je gaat joinen

Ik gok dat als je dat 3e punt oplost, dat je ook meteen een groot verschil gaat merken tussen wel of geen TRIM gebruiken. Vanwege de charset verschillen kan MySQL nml geen index gebruiken.

[Fiander]

is een van onderstaande queries traag ?

SQL:

SELECT d.*
FROM   icecat_data d
WHERE  d.data IS NOT NULL
       AND d.invalid = 0
       AND d.pending = 1

SQL:

SELECT 
       a.*
FROM  artikeldata.artik
ORDER  BY a.a_artstat DESC

je geeft zelf aan dat de tabel eerst vooraf trimmen, en de querie daarna zonder trim uit te voeren geen verschil maakt.

en hoe vaak wijzigt de a tabel? en hoe up to date heb je die nodig? misschien deze tabel lokaal in je andere database copieren mbv een cron job, en tijdens dat copieren de join kolom alvast te casten naar een int, en een index over die kolom te plaatsen.
zolang je een functie over een kolom gebruikt, zijn indexen bijna nutteloos.

[ Voor 5% gewijzigd door Fiander op 29-06-2012 10:34 . Reden: toevoeging ]

[PaulEm]

Fiander schreef op vrijdag 29 juni 2012 @ 10:31:
is een van onderstaande queries traag ?

SQL:

SELECT d.*
FROM   icecat_data d
WHERE  d.data IS NOT NULL
       AND d.invalid = 0
       AND d.pending = 1

code:

Toon Records 0 - 29 (1,237 totaal, Query duurde 0.0031 sec)

Fiander schreef op vrijdag 29 juni 2012 @ 10:31:

SQL:

SELECT 
       a.*
FROM  artikeldata.artikels a
ORDER  BY a.a_artstat DESC

code:

Toon Records 0 - 29 (~17,4411 totaal, Query duurde 0.1730 sec) [a_artstat: P - P]

_JGC_ schreef op vrijdag 29 juni 2012 @ 10:30:
Ik zie 3 mogelijke performanceproblemen:
- TRIM in je JOIN, maar volgens jou maakt dit niks uit
- Je joint InnoDB aan MyISAM. Grote kans dat als je je MyISAM tabel ook InnoDB maakt dat je, mits je server getuned is voor InnoDB, een stuk minder performanceproblemen hebt
- Je charset voor de ene tabel is UTF8, voor de andere Latin1. Dit zorgt voor extra onnodige conversies. Maak die twee gelijk, iig op het veld dat je gaat joinen

Ik gok dat als je dat 3e punt oplost, dat je ook meteen een groot verschil gaat merken tussen wel of geen TRIM gebruiken. Vanwege de charset verschillen kan MySQL nml geen index gebruiken.

Bedankt, hier ga ik zeker even naar kijken.

Edit: Dit was inderdaad de boosdoener, met een TRIM is het nog steeds sloom, zonder TRIM is de query gewoon weer lekker snel. Bedankt

[ Voor 44% gewijzigd door PaulEm op 29-06-2012 10:55 ]

[cariolive23]

Heb je al eens met EXPLAIN gekeken hoe de database de query uitvoert? En welke index er wordt gebruikt? Zonder EXPLAIN is het erg lastig om een query te optimaliseren.

[WouZz]

Ennuh... what about.. eerst een UPDATE draaien om a.a_reflev te trimmen? Of heb je daar geen rechten voor?