[MySQL] hoe te optimaliseren?

[_Gekkie_]

Ik weet niet helemaal zeker of dit passend is, maar niet geschoten is altijd mis:
Ik heb een query in Mysql:

MySQL:

SELECT   DISTINCT `base`.`id` 
FROM     `person`, 
         `base` 
         LEFT JOIN `attributeValue` AS `customValue1510623` 
           ON (`person`.`id` = `customValue1510623`.`objectId` 
               AND `customValue1510623`.`attributeId` = '1510623') 
         LEFT JOIN `attributeValue` AS `customValue3240914` 
           ON (`person`.`id` = `customValue3240914`.`objectId` 
               AND `customValue3240914`.`attributeId` = '3240914') 
         LEFT JOIN `attributeValue` AS `customValue3391489` 
           ON (`person`.`id` = `customValue3391489`.`objectId` 
               AND `customValue3391489`.`attributeId` = '3391489') 
         LEFT JOIN `attributeValue` AS `customValue3654249` 
           ON (`person`.`id` = `customValue3654249`.`objectId` 
               AND `customValue3654249`.`attributeId` = '3654249') 
         LEFT JOIN `attributeValue` AS `customValue3825051` 
           ON (`person`.`id` = `customValue3825051`.`objectId` 
               AND `customValue3825051`.`attributeId` = '3825051') 
         LEFT JOIN `attributeValue` AS `customValue4436777` 
           ON (`person`.`id` = `customValue4436777`.`objectId` 
               AND `customValue4436777`.`attributeId` = '4436777') 
         LEFT JOIN `attributeValue` AS `customValue4436778` 
           ON (`person`.`id` = `customValue4436778`.`objectId` 
               AND `customValue4436778`.`attributeId` = '4436778') 
         LEFT JOIN `attributeValue` AS `customValue4572145` 
           ON (`person`.`id` = `customValue4572145`.`objectId` 
               AND `customValue4572145`.`attributeId` = '4572145') 
         LEFT JOIN `attributeValue` AS `customValue4841024` 
           ON (`person`.`id` = `customValue4841024`.`objectId` 
               AND `customValue4841024`.`attributeId` = '4841024') 
         LEFT JOIN `attributeValue` AS `customValue4841025` 
           ON (`person`.`id` = `customValue4841025`.`objectId` 
               AND `customValue4841025`.`attributeId` = '4841025') 
         LEFT JOIN `attributeValue` AS `customValue4841026` 
           ON (`person`.`id` = `customValue4841026`.`objectId` 
               AND `customValue4841026`.`attributeId` = '4841026') 
         LEFT JOIN `attributeValue` AS `customValue4841029` 
           ON (`person`.`id` = `customValue4841029`.`objectId` 
               AND `customValue4841029`.`attributeId` = '4841029') 
         LEFT JOIN `attributeValue` AS `customValue4841032` 
           ON (`person`.`id` = `customValue4841032`.`objectId` 
               AND `customValue4841032`.`attributeId` = '4841032') 
         LEFT JOIN `attributeValue` AS `customValue4841033` 
           ON (`person`.`id` = `customValue4841033`.`objectId` 
               AND `customValue4841033`.`attributeId` = '4841033') 
         LEFT JOIN `attributeValue` AS `customValue4841035` 
           ON (`person`.`id` = `customValue4841035`.`objectId` 
               AND `customValue4841035`.`attributeId` = '4841035') 
         LEFT JOIN `attributeValue` AS `customValue4853550` 
           ON (`person`.`id` = `customValue4853550`.`objectId` 
               AND `customValue4853550`.`attributeId` = '4853550') 
         LEFT JOIN `attributeValue` AS `customValue4853551` 
           ON (`person`.`id` = `customValue4853551`.`objectId` 
               AND `customValue4853551`.`attributeId` = '4853551') 
         LEFT JOIN `attributeValue` AS `customValue4853552` 
           ON (`person`.`id` = `customValue4853552`.`objectId` 
               AND `customValue4853552`.`attributeId` = '4853552') 
         LEFT JOIN `attributeValue` AS `customValue4853553` 
           ON (`person`.`id` = `customValue4853553`.`objectId` 
               AND `customValue4853553`.`attributeId` = '4853553') 
         LEFT JOIN `attributeValue` AS `customValue4853554` 
           ON (`person`.`id` = `customValue4853554`.`objectId` 
               AND `customValue4853554`.`attributeId` = '4853554') 
         LEFT JOIN `attributeValue` AS `customValue4853556` 
           ON (`person`.`id` = `customValue4853556`.`objectId` 
               AND `customValue4853556`.`attributeId` = '4853556') 
         LEFT JOIN `attributeValue` AS `customValue4873839` 
           ON (`person`.`id` = `customValue4873839`.`objectId` 
               AND `customValue4873839`.`attributeId` = '4873839') 
         LEFT JOIN `attributeValue` AS `customValue4951823` 
           ON (`person`.`id` = `customValue4951823`.`objectId` 
               AND `customValue4951823`.`attributeId` = '4951823') 
         LEFT JOIN `attributeValue` AS `customValue9932186` 
           ON (`person`.`id` = `customValue9932186`.`objectId` 
               AND `customValue9932186`.`attributeId` = '9932186') 
         LEFT JOIN `message` 
           ON (`person`.`id` = `message`.`objectId`) 
WHERE    ((CONCAT_WS(" ",`person`.`title`,`person`.`firstName`, 
            `person`.`middleName`,`person`.`lastName`, 
            `person`.`email`) LIKE '%Charissa%') 
  OR (`customValue1510623`.`value` LIKE '%Charissa%') 
  OR (`customValue3240914`.`value` LIKE '%Charissa%') 
  OR (`customValue3391489`.`value` LIKE '%Charissa%') 
  OR (`customValue3654249`.`value` LIKE '%Charissa%') 
  OR (`customValue3825051`.`value` LIKE '%Charissa%') 
  OR (`customValue4436777`.`value` LIKE '%Charissa%') 
  OR (`customValue4436778`.`value` LIKE '%Charissa%') 
  OR (`customValue4572145`.`value` LIKE '%Charissa%') 
  OR (`customValue4841024`.`value` LIKE '%Charissa%') 
  OR (`customValue4841025`.`value` LIKE '%Charissa%') 
  OR (`customValue4841026`.`value` LIKE '%Charissa%') 
  OR (`customValue4841029`.`value` LIKE '%Charissa%') 
  OR (`customValue4841032`.`value` LIKE '%Charissa%') 
  OR (`customValue4841033`.`value` LIKE '%Charissa%') 
  OR (`customValue4841035`.`value` LIKE '%Charissa%') 
  OR (`customValue4853550`.`value` LIKE '%Charissa%') 
  OR (`customValue4853551`.`value` LIKE '%Charissa%') 
  OR (`customValue4853552`.`value` LIKE '%Charissa%') 
  OR (`customValue4853553`.`value` LIKE '%Charissa%') 
  OR (`customValue4853554`.`value` LIKE '%Charissa%') 
  OR (`customValue4853556`.`value` LIKE '%Charissa%') 
  OR (`customValue4873839`.`value` LIKE '%Charissa%') 
  OR (`customValue4951823`.`value` LIKE '%Charissa%') 
  OR (`customValue9932186`.`value` LIKE '%Charissa%') 
  OR (`message`.`content` LIKE '%Charissa%')) 
AND ((Concat_ws(" ",`person`.`title`,`person`.`firstName`, 
                `person`.`middleName`,`person`.`lastName`, 
                `person`.`email`) LIKE '%Perrenet%') 
      OR (`customValue1510623`.`value` LIKE '%Perrenet%') 
      OR (`customValue3240914`.`value` LIKE '%Perrenet%') 
      OR (`customValue3391489`.`value` LIKE '%Perrenet%') 
      OR (`customValue3654249`.`value` LIKE '%Perrenet%') 
      OR (`customValue3825051`.`value` LIKE '%Perrenet%') 
      OR (`customValue4436777`.`value` LIKE '%Perrenet%') 
      OR (`customValue4436778`.`value` LIKE '%Perrenet%') 
      OR (`customValue4572145`.`value` LIKE '%Perrenet%') 
      OR (`customValue4841024`.`value` LIKE '%Perrenet%') 
      OR (`customValue4841025`.`value` LIKE '%Perrenet%') 
      OR (`customValue4841026`.`value` LIKE '%Perrenet%') 
      OR (`customValue4841029`.`value` LIKE '%Perrenet%') 
      OR (`customValue4841032`.`value` LIKE '%Perrenet%') 
      OR (`customValue4841033`.`value` LIKE '%Perrenet%') 
      OR (`customValue4841035`.`value` LIKE '%Perrenet%') 
      OR (`customValue4853550`.`value` LIKE '%Perrenet%') 
      OR (`customValue4853551`.`value` LIKE '%Perrenet%') 
      OR (`customValue4853552`.`value` LIKE '%Perrenet%') 
      OR (`customValue4853553`.`value` LIKE '%Perrenet%') 
      OR (`customValue4853554`.`value` LIKE '%Perrenet%') 
      OR (`customValue4853556`.`value` LIKE '%Perrenet%') 
      OR (`customValue4873839`.`value` LIKE '%Perrenet%') 
      OR (`customValue4951823`.`value` LIKE '%Perrenet%') 
      OR (`customValue9932186`.`value` LIKE '%Perrenet%') 
      OR (`message`.`content` LIKE '%Perrenet%')) 
AND (`person`.`id` = `base`.`id`) 
AND (`base`.`implementationId` IN (1508457)) 
AND (`base`.`deleted` = 0) 
AND (`person`.`_implementationId` IN (1508457)) 
AND (`person`.`_deleted` = 0) 
ORDER BY `person`.`lastName`, 
         `person`.`firstName` ASC, 
         `person`.`id` DESC;

Zoals je ziet nogal lang Probleem is er is een tabel 'attributeValue' waarin allerlei waarden voor attributen staan... Deze worden tegen de basis tabel `person` aangehangen en daarbinnen wordt gezocht... Klinkt simpel niet waar? Tja... Totdat het nogal wat attributen worden zoals bovenstaand... Mijn vraag is dus: Kan zoiets nou niet korter en wellicht wat geoptimaliseerder? Mijn slow log raakt hier zo vol van

[Patriot]

Wat een draak van een query zeg. Volgens mij moet je meer gaan veranderen aan het ontwerp van de database dan aan de query, want met een slecht databaseontwerp krijg je ook altijd slechte queries.

[_Gekkie_]

Patriot schreef op maandag 17 augustus 2009 @ 16:23:
Wat een draak van een query zeg. Volgens mij moet je meer gaan veranderen aan het ontwerp van de database dan aan de query, want met een slecht databaseontwerp krijg je ook altijd slechte queries.

Mee eens, maar die draak van een tabel (in dit geval 'attributevalue' is er nu eenmaal en dat kan ik niet veranderen... helaas)

Ik was al bang dat daar de focus op zou komen, maar daar kom ik niet 1-2-3 onderuit helaas. Daarnaast is dit een gegenereerde query, dus zelf typen doe ik dat nou ook weer niet

[Dido]

Je joined dezelfde tabel xx keer om er telkens een recordje met id, value bij te halen.

Wat zou er niet werken aan iets als:

SQL:

Select blaat from Table T
Join AttributeTable A on T.Id = A.ObjectId
Where A.AttributeId in (132438, 17813, 24957249, 190471230, 1891308, 1391741389)
And A.Value like '%Charissa%'

Nog even los van het feit dat je "person"en "Base" niet expliciet joined. Dat zou ik sowieso maar even regelen, dat versnelt wel wat, waarschijnlijk. (Een distinct over een cartesisch product staat wel netjes, maar het is niet performant)

[ Voor 28% gewijzigd door Dido op 17-08-2009 16:32 ]

[Woy]

Ah we hebben weer het database in database anti-pattern

Maar buiten je query die compleet niet leesbaar is, is een van je grote problemen dat je textvelden aan het doorzoeken bent met '%.....%', door het startende % teken kan een eventuele index al niet meer gebruikt worden. Er zal dus minstens al een FULL TABLE SCAN gedaan moeten worden.

Eventueel kun je nog kijken of je iets als volgt kunt doen

SQL:

SELECT p.name
FROM Person p
WHERE p.Id in 
    ( SELECT a.PersonId
      FROM attributeValues a
      WHERE a.value LIKE '%....%'
      OR a.Value LIKE '%....%' )

[_Gekkie_]

Woy schreef op maandag 17 augustus 2009 @ 16:35:
Ah we hebben weer het database in database anti-pattern

Zoals gezegd, ik wist dat ervan zou komen: de database-anitpattern... Tja. Klopt. Mee eens. Maar destijds (4 jaar geleden) was het de beste oplossing om in een standaard systeem dynamisch velden toe te kennen aan objecten... Oftewel 1 systeem waarbij object A in de ene context een extra attribuut heeft, maar in de andere context niet

Dido schreef op maandag 17 augustus 2009 @ 16:30:
Je joined dezelfde tabel xx keer om er telkens een recordje met id, value bij te halen.

Wat zou er niet werken aan iets als:

SQL:

Select blaat from Table T
Join AttributeTable A on T.Id = A.ObjectId
Where A.AttributeId in (132438, 17813, 24957249, 190471230, 1891308, 1391741389)
And A.Value like '%Charissa%'

Nog even los van het feit dat je "person"en "Base" niet expliciet joined. Dat zou ik sowieso maar even regelen, dat versnelt wel wat, waarschijnlijk. (Een distinct over een cartesisch product staat wel netjes, maar het is niet performant)

De base->person relatie is via een from / where constructie ivm de generalisatie van de query generator... Het is dus stiekum wel een harde relatie (en de where is ook 1 op 1, dus dat zit wel snor) Het nadeel van de door jou voorgestelde methode is dat ik niet 100% zeker kan weten dat er voor alle velden een record in de attributeValue tabel is... Oftewel: ik kan geen harde relatie leggen... ? (en dus kan de where niet...)

Dat is ook de reden voor de left join: een null result geeft niet, maar een inner join / where kan ik niet forceren ivm het eventueel ontbreken van records... (of zie ik iets over het hoofd?)

[ Voor 16% gewijzigd door _Gekkie_ op 17-08-2009 16:38 ]

[roeleboel]

Dido schreef op maandag 17 augustus 2009 @ 16:30:
Nog even los van het feit dat je "person"en "Base" niet expliciet joined.

gebeurt dat niet op regel 134?

[creator1988]

Kan je niet maken wat je wilt met een pivot table?

[Dido]

_Gekkie_ schreef op maandag 17 augustus 2009 @ 16:35:
De base->person relatie is via een from / where constructie ivm de generalisatie van de query generator... Het is dus stiekum wel een harde relatie (en de where is ook 1 op 1, dus dat zit wel snor)

Ah, ok.
Dan kun je (als het inderdaad regel 34 betreft) gewoon een join on person.id=base.id doen, lijkt me?

Het nadeel van de door jou voorgestelde methode is dat ik niet 100% zeker kan weten dat er voor alle velden een record in de attributeValue tabel is... Oftewel: ik kan geen harde relatie leggen... ? (en dus kan de where niet...)

Waarom zou de where niet kunnen? Tenzij ik me heel sterk vergis is dat geen enkel probleem.

Persoon: ID = 1
Base: ID = 1
Attribute: ID= 1, BaseId = 1, Value = 'Paard'
Attribute: ID= 2, BaseId = 1, Value = 'Paardebloem'
Attribute: ID= 4, BaseId = 1, Value = 'Paardelul'

dan geeft

SQL:

select P.ID, A.Value
from (Persoon P join Base B on P.ID = B.ID)
join Attribute A on A.BaseID = B.ID
where A.ID in (1,2,3,4)
and A.Value like '%Paard%'

Gewoon resultaten voor attributes 1, 2 en 4.

Dat de inner join tussen Base en Attribute geen record teruggeeft voor Attribute 3 is toch geen probleem?
Met een left outer join zou de join wel een record teruggeven, maar zou de LIKE clause altijd false teruggeven, dus zouden die records ook uit je resultset gefilterd worden.

Dat is ook de reden voor de left join: een null result geeft niet, maar een inner join / where kan ik niet forceren ivm het eventueel ontbreken van records... (of zie ik iets over het hoofd?)

Een inner join "forceer" je niet, een inner join geeft gewoon geen results voor records die niet aan beide kanten van de join een non-null value hebben. Maar die results wil je helemaal niet.

Maar het kan uiteraard goed zijn dat ik nog steeds iets enorm over het hoofd zie, je query is niet de meest leesbare

[BCC]

Je beperkt dan misschien het aantal joins, maar je zit nog altijd met je full table scan.

[Dido]

BCC schreef op maandag 17 augustus 2009 @ 20:24:
Je beperkt dan misschien het aantal joins, maar je zit nog altijd met je full table scan.

Klopt, maar ik zie het enorm vergroten van lees- en onderhoudbaarheid ook als optimalisatie

Ik weet natuurlijk niet wat voor indexen er al zijn, en in hoeverre MySQL daar gebruik van maakt (of kan maken). Ik heb alleen zo'n vaag vermoeden dat je tegen een database-redesign aan gaat lopen.

Het zou misschien al wel helpen om de te selecteren attributeID's in een aparte table onder te brengen en die te joinen - dan ben je van je IN af. Blijf je zitten met je zware (?) LIKE clause natuurlijk.

[cariolive23]

maar je zit nog altijd met je full table scan.

Zonder fulltext index ga je daar ook niets aan veranderen.

Maar, is het een probleem dat er een sequential scan wordt uitgevoerd? Wanneer het maar een paar records betreft, kan de database zelf ook al voor een seqscan kiezen omdat dit sneller kan zijn dan een index scan. Dat ligt helemaal aan de situatie en vooral aan het aantal records. EXPLAIN kan je daar meer over vertellen. EXPLAIN is toch al onmisbaar wanneer je wilt gaan optimaliseren, zonder EXPLAIN heb je geen idee wat je aan het doen bent. Zorg er wel voor dat je de cache uitzet, anders ben je de cache aan het analyzeren...

[_Gekkie_]

Hmmm... tja, uit de explain kom ik zoveel wijzer als dat de person tabel primair wordt gebruikt en de rest er 1op1 tegenaankomt...:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	person	ref	PRIMARY,_implementationId,_deleted	_implementationId	4	const	19656	Using where; Using temporary; Using filesort
1	SIMPLE	customValue1510623	eq_ref	PRIMARY,objectId	PRIMARY	8	const,core.person.id	1
enz
1	SIMPLE	message	ref	objectId	objectId	4	core.person.id	1	Using where; Distinct

Dus tja... het zal dus aan het db-ontwerp liggen, dat is wel duidelijk... Maar dat omzetten wordt nog een pittige klus: hoe kan ik er eenvoudig voor zorgen dat ik dynamisch attributen kan toekennen zonder dus te werken met het antipattern? (en het ook nog lekker kan doorzoeken )

[Woy]

De vraag is of je echt zoveel dynamische attributen nodig hebt. Vaak kan je volstaan met een middenweg. Bij personen weet je vaak al een hele hoop attributen die er zijn. Door deze gewoon op te nemen bij je persoon word je tabel met dynamische attributen al een stuk kleiner, en zal er dus ook een stuk minder overhead zijn om een full table scan te doen.

Je zult je dus echt af moeten vragen hoe dynamisch de attributen echt zijn.

Verder is een van je problemen zowiezo dat je in text-velden zoekt, en dus geen gebruik maakt van de indexen. Als je dat wilt verbeteren zul je naar fulltext indexing moeten kijken.

[Voutloos]

En bekijk de explain eens van wat er gebeurd als je de query opstelt zoals Dido het zegt. Dan moet er in ieder geval op basis van een index al goed gefilterd kunnen worden.

Is nog steeds anti-pattern, maar idioot veel leesbaarder en naar alle waarschijnlijkheid al behoorlijk wat sneller.

[ Voor 25% gewijzigd door Voutloos op 18-08-2009 10:26 ]

[_Gekkie_]

Toch begrijp ik de oplossing van Dido niet... Ik heb het zelf proberen te reproduceren, maar met de oude query vind ik een resultaat, met de nieuwe niet:

code:

SELECT SQL_NO_CACHE DISTINCT `base`.`id`
FROM (`person` JOIN `base` ON (`person`.`id` = `base`.`id` AND `base`.`deleted`=0 AND `base`.`implementationId` = 1508457 AND `person`.`_deleted`=0 AND `person`.`_implementationId` = 1508457))
JOIN `attributeValue` AS `attributeValues` ON (`attributeValues`.`objectId` = `base`.`id`)
JOIN `message` ON (`message`.`objectId` = `base`.`id`)
WHERE
`attributeValues`.`attributeId` in (1510623,3240914,3391489,3654249,3825051,4436777,4436778,4572145,4841024,4841025,4841026,4841029,4841032,4841033,4841035,4853550,4853551,4853552,4853553,4853554,4853556,4873839,4951823,9932186)
AND
(CONCAT_WS(" ", `person`.`title`, `person`.`firstName`, 
    `person`.`middleName`, `person`.`lastName`, 
    `person`.`email`, `attributeValues`.`value`, `message`.`content`) LIKE '%keyword%')
ORDER BY `person`.`lastName`, `person`.`firstName` ASC, `person`.`id` DESC;

Totdat ik de JOIN attributeValue (en JOIN message) omzet naar een `LEFT JOIN`... Dat komt dan toch doordat er geen attribute gejoind kan worden? Alleen als ik er een left join én de attributeids opneem in de join, werkt het...
Oftewel:

code:

SELECT SQL_NO_CACHE `base`.`id`, attributeValues.value, CONCAT_WS(" ", `person`.`title`, `person`.`firstName`,  `person`.`middleName`, `person`.`lastName`,  `person`.`email`, `attributeValues`.`value`, `message`.`content`)
FROM (`person` JOIN `base` ON (`person`.`id` = `base`.`id` AND `base`.`deleted`=0 AND `base`.`implementationId` = 1508457 AND `person`.`_deleted`=0 AND `person`.`_implementationId` = 1508457))
LEFT JOIN `attributeValue` AS `attributeValues` ON (`attributeValues`.`objectId` = `base`.`id` AND `attributeValues`.`attributeId` in (1510623,3240914,3391489,3654249,3825051,4436777,4436778,4572145,4841024,4841025,4841026,4841029,4841032,4841033,4841035,4853550,4853551,4853552,4853553,4853554,4853556,4873839,4951823,9932186))
LEFT JOIN `message` ON (`message`.`objectId` = `base`.`id`)
where CONCAT_WS(" ", `person`.`title`, `person`.`firstName`,  `person`.`middleName`, `person`.`lastName`,  `person`.`email`, `attributeValues`.`value`, `message`.`content`) LIKE '% keyword%'
ORDER BY
    `person`.`lastName`,
    `person`.`firstName` ASC,
    `person`.`id` DESC;

hmmmmmm

Wat ik van de explain verder zie is dat het totaal aantal rijen wat bekeken wordt min of meer gelijk is... Uiteraard minder resultaat in de explain zelf ivm het feit dat alle attributeValues via 1 join gemaakt worden ipv stuk voor stuk... Daar staat wel weer tegen over dat volgens mij de cartessian product verhoogd is:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	person	ref	PRIMARY,_implementationId,_deleted	_implementationId	4	const	19726	Using where; Using temporary; Using filesort
1	SIMPLE	base	eq_ref	PRIMARY,implementationId,deleted	PRIMARY	4	core.person.id	1	Using where
1	SIMPLE	attributeValues	ref	PRIMARY,objectId	objectId	4	core.person.id	12	Distinct
1	SIMPLE	message	ref	objectId	objectId	4	core.person.id	1	Using where; Distinct

De snelheid van de query (die wel resultaten levert) is van 2.0s naar 1.3 s dus dat is al wel weer meegenomen zucht... soms snap je er toch niets van

[_Gekkie_]

Woy schreef op dinsdag 18 augustus 2009 @ 10:21:
De vraag is of je echt zoveel dynamische attributen nodig hebt. Vaak kan je volstaan met een middenweg. Bij personen weet je vaak al een hele hoop attributen die er zijn. Door deze gewoon op te nemen bij je persoon word je tabel met dynamische attributen al een stuk kleiner, en zal er dus ook een stuk minder overhead zijn om een full table scan te doen.

Je zult je dus echt af moeten vragen hoe dynamisch de attributen echt zijn.

Verder is een van je problemen zowiezo dat je in text-velden zoekt, en dus geen gebruik maakt van de indexen. Als je dat wilt verbeteren zul je naar fulltext indexing moeten kijken.

De context is dat het systeem (en daarmee de DB) in veel (lees 200+) omgevingen wordt gebruikt. Natuurlijk is er een standaard voor de 'person' maar er zijn er ook die 'lidnummer' of 'registratiecode' ook opslaan... (of 12 extra dergelijke custom-velden) Daarbij is `person` niet het enige object wat extra velden kan krijgen, in totaal zo'n 30 objectsoorten kunnen uiteenloopende attributen op deze wijze toegekend krijgen... Deze worden vervolgens allemaal (althans de waarden ervan) in die tabel weggeschreven... (onderhand 10M rijen, 800MB aan totale grootte)

[Dido]

_Gekkie_ schreef op dinsdag 18 augustus 2009 @ 15:06:
Toch begrijp ik de oplossing van Dido niet... Ik heb het zelf proberen te reproduceren, maar met de oude query vind ik een resultaat, met de nieuwe niet:

code:

SELECT SQL_NO_CACHE DISTINCT `base`.`id`
FROM (`person` JOIN `base` ON (`person`.`id` = `base`.`id` AND `base`.`deleted`=0 AND `base`.`implementationId` = 1508457 AND `person`.`_deleted`=0 AND `person`.`_implementationId` = 1508457))
JOIN `attributeValue` AS `attributeValues` ON (`attributeValues`.`objectId` = `base`.`id`)
JOIN `message` ON (`message`.`objectId` = `base`.`id`)
WHERE
`attributeValues`.`attributeId` in (1510623,3240914,3391489,3654249,3825051,4436777,4436778,4572145,4841024,4841025,4841026,4841029,4841032,4841033,4841035,4853550,4853551,4853552,4853553,4853554,4853556,4873839,4951823,9932186)
AND
(CONCAT_WS(" ", `person`.`title`, `person`.`firstName`, 
    `person`.`middleName`, `person`.`lastName`, 
    `person`.`email`, `attributeValues`.`value`, `message`.`content`) LIKE '%keyword%')
ORDER BY `person`.`lastName`, `person`.`firstName` ASC, `person`.`id` DESC;

Nogal wiedes, want ik had de message table over het hoofd gezien. Je joined nu je messages op je result van de join (person-join)+attributes. Als er dan geen attributes zijn, heb je ook geen messages. Dan biedt een left outer join inderdada uitkomst.
Of je de attributes in de join opneemt maakt denk ik weinig uit.

Je query zou trouwens nog leesbaarder worden als je je tabellen aliased met korte namen (B = base, A=Atribute, etc), hoewel dat bij gegenereerde queries niet altijd handig is.

Je sort-order is trouwens wat vaag - je select alleen het baseID en sorteert eerst op twee onzichtbare velden

[BCC]

_Gekkie_ schreef op dinsdag 18 augustus 2009 @ 15:09:
[...]
De context is dat het systeem (en daarmee de DB) in veel (lees 200+) omgevingen wordt gebruikt. Natuurlijk is er een standaard voor de 'person' maar er zijn er ook die 'lidnummer' of 'registratiecode' ook opslaan... (of 12 extra dergelijke custom-velden)

Ik zou toch een gedeelte van die dynamische attributen (de meest gebruikte?) in ieder geval proberen om te schrijven naar kolomen. Dat die velden dan in een hoop rijen leeg blijven is niet erg voor je performance. 12 mogelijk lege Kolommen toevoegen is een stuk duidelijker en sneller dan de constructie hierboven.

Om te beginnen kun je natuurlijk ook beide doen: Zorg in je object laag dat bepaalde attributen in kolomen "gecached worden", zodat je daar in mysql eenvoudig op kan selecteren.

Daarbij is `person` niet het enige object wat extra velden kan krijgen, in totaal zo'n 30 objectsoorten kunnen uiteenloopende attributen op deze wijze toegekend krijgen... Deze worden vervolgens allemaal (althans de waarden ervan) in die tabel weggeschreven... (onderhand 10M rijen, 800MB aan totale grootte)

Ik zou eerst maar eens beginnen om het database-antipattern te doorbreken op het plek waar je het meeste last hebt. Slecht design is nooit op te lossen door een magisch stukje code.

Een even out of the box denkend: Je zoekt nu in alle attribute values naar een bepaalde string. Je kan dat natuurlijk ook omdraaien door eerst de value op te zoeken en daarna pas een person te joinen.

code:

SELECT objectId from `base` WHERE attributeValue LIKE %jantje% GROUP BY objectId

En alleen voor de dan opgelepelde resultaten met een tweede of subquery alle attribuut ellende ophalen + eventueel andere matchende velden.

[ Voor 53% gewijzigd door BCC op 18-08-2009 18:28 ]

[_js_]

Volgens die explain is er mogelijk nog wel wat ruimte tot optimalisatie in de Person tabel.

Welk deel van de tabel wordt weggefilterd met de index op _implementationId? Is dat 1% of 99%? Of iets er tussen in?

Wat waarschijnlijk ook tijd scheelt is om niet de attribute value mee te nemen in de concat in je where clausule, maar deze apart te doorzoeken, je doet nu 12 keer zoekacties op dezelfde text in de person en message tabellen.

[_Gekkie_]

Dido schreef op dinsdag 18 augustus 2009 @ 16:51:
[...]

Nogal wiedes, want ik had de message table over het hoofd gezien. Je joined nu je messages op je result van de join (person-join)+attributes. Als er dan geen attributes zijn, heb je ook geen messages. Dan biedt een left outer join inderdada uitkomst.
Of je de attributes in de join opneemt maakt denk ik weinig uit.

Je query zou trouwens nog leesbaarder worden als je je tabellen aliased met korte namen (B = base, A=Atribute, etc), hoewel dat bij gegenereerde queries niet altijd handig is.

Tja, ze zijn gegenereerd, dus op zich maken kortere aliassen niet uit. Zo zie je wel direct (bij het nalezen van de slowlogs e.d.) waar het om gaat zonder dat je tig aliassen moet nazoeken e.d. Kleine details

Dido schreef op dinsdag 18 augustus 2009 @ 16:51:
[...]
Je sort-order is trouwens wat vaag - je select alleen het baseID en sorteert eerst op twee onzichtbare velden

Het systeem doet een twee stappen plan:
als ik objecten terug wil hebben, zoek ik eerst de ID's (in een bepaalde volgorde) daarna zoek ik de inhoud van de objecten via een 'select attribuut, attribuut, from bla bla bla where id IN ()' zodat ik alleen de velden ophaal van de objecten die ik uberhaupt wil hebben. (we vullen daarna in 1 keer de hele bups aan objecten in het register)

BCC schreef op dinsdag 18 augustus 2009 @ 18:15:
[...]

Ik zou toch een gedeelte van die dynamische attributen (de meest gebruikte?) in ieder geval proberen om te schrijven naar kolomen. Dat die velden dan in een hoop rijen leeg blijven is niet erg voor je performance. 12 mogelijk lege Kolommen toevoegen is een stuk duidelijker en sneller dan de constructie hierboven.

Om te beginnen kun je natuurlijk ook beide doen: Zorg in je object laag dat bepaalde attributen in kolomen "gecached worden", zodat je daar in mysql eenvoudig op kan selecteren.

[...]

Ik zou eerst maar eens beginnen om het database-antipattern te doorbreken op het plek waar je het meeste last hebt. Slecht design is nooit op te lossen door een magisch stukje code.

Je hebt gelijk dat er ook het probleem is van de antipattern en het toevoegen van rijen, maar als je het grotere plaatje ziet, is het (helaas) niet zo eenvoudig... Ik ben er na 4 jaar mee gewerkt te hebben ook niet super tevreden meer over en de nieuwe versie zal het anders oplossen, maar dit is nu eenmaal de context..

Dido schreef op dinsdag 18 augustus 2009 @ 16:51:

Een even out of the box denkend: Je zoekt nu in alle attribute values naar een bepaalde string. Je kan dat natuurlijk ook omdraaien door eerst de value op te zoeken en daarna pas een person te joinen.

code:

SELECT objectId from `base` WHERE attributeValue LIKE %jantje% GROUP BY objectId

En alleen voor de dan opgelepelde resultaten met een tweede of subquery alle attribuut ellende ophalen + eventueel andere matchende velden.

Dat is inderdaad ook een optie, ware het niet dat de attributeValue tabel niet te indexen is zonder een FULL TEXT index.. (waar je weer tegen innodb problemen aanloopt... zucht) Ik heb zelf nog zitten stoeien met het idee om per attribuut soort (integer, boolean, longtext e.d.) andere kolommen te gebruiken.. maar dat ging me toch écht te ver in de wereld van de antipattern (en daarbij kan ik nu generiek de tabel gebruiken zonder dat ik weet wat voor soort attribuut attribuut ID 2 is)

_js_ schreef op dinsdag 18 augustus 2009 @ 21:55:
Volgens die explain is er mogelijk nog wel wat ruimte tot optimalisatie in de Person tabel.

Welk deel van de tabel wordt weggefilterd met de index op _implementationId? Is dat 1% of 99%? Of iets er tussen in?

Wat waarschijnlijk ook tijd scheelt is om niet de attribute value mee te nemen in de concat in je where clausule, maar deze apart te doorzoeken, je doet nu 12 keer zoekacties op dezelfde text in de person en message tabellen.

Er staan 400K rijen in de person tabel (18 kolommen) en 17.034 daarvan voldoen aan de _implementationId clause... Misschien is de query van Dido wel het beste wat er in deze situatie te bakken is... ?

Soort van 'vlag op een modderschuit' idee