Ingewikkelde query

[oogapp0ltje]

Ben bezig met een database met woorden welke gekoppeld zijn aan een item.
Met de volgende query kan ik zoeken op een woord en eveneens de overige woorden vinden die in hetzelfde 'item' voorkomen.

Het nounID 69 representeert een bepaald woord, de omspannende query vindt de overige woorden die in dezelfde items voorkomen als waarin woord 69 voorkomt.

Het is relatief eenvoudig om dit op te lossen met een subquery, de performance is alleen erg traag. Een query duurt gem. 4 seconden op een DB van 20k nouns. Daar komt nog bij dat ik nu alleen nog maar zoek op woord 69.

Hebben jullie een idee hoe ik de performance van deze query kan vergroten? De table is voorzien van indexen.

SQL:

select i_n2.nounID, count(i_n2.nounID) as cnt from itemnouns i_n2 
left join nouns n2 on n2.nounID = i_n2.nounID
where 
n2.navigatable = 0
and i_n2.itemID in 
(
   select i.itemID from items i 
   left join itemnouns i_n on i_n.itemID = i.itemID
   left join nouns n on i_n.nounID = n.nounID
   where i_n.nounID=69 
)
group by nounID

[ Voor 0% gewijzigd door een moderator op 24-05-2008 17:35 . Reden: highlighting aangezet ]

[T.T.]

lijkt een beetje op full-text search? Daar blinkt MySQL nooit zo erg in uit, maar is wel genoeg documentatie over te vinden (linkje). Je kan het ook over een geheel andere (niet-DB) boeg gooien, maar dat wil je wellicht niet. Mocht je dat wel leuk vinden, probeer dan eens Solr of Sphinx (MySql gebaseerd)

edit: ik zou moeten toevoegen dat een lading subqueries, joins en group-by's nooit helpt. Simpele queries zijn sneller, dus probeer je probleem op te lossen waarbij je dat vermijdt. Dat betekent dus dingen zoals een inverted index vooraf opbouwen (of laten opbouwen)

[ Voor 41% gewijzigd door T.T. op 24-05-2008 17:47 ]

[JasperE]

Gebruik je MySQL? Zo ja, welke storage engine? Heb je EXPLAIN output al geinterpreteerd? Welke indexen heb je aangemaakt?

[ Voor 44% gewijzigd door JasperE op 24-05-2008 17:59 ]

[WormLord]

Geen idee of het echt sneller is, maar volgens mij is die query ook te schrijven zonder subquerie en met minder joins.
Je krijgt dan iets als:

SQL:

select i_n2.nounID, count(i_n2.nounID) as cnt
  from itemnouns i_n
  left join itemnouns i_n2 on i_n2.itemID = i_n.itemID
  left join nouns n2 on n2.nounID = i_n2.nounID 
 where i_n.nounID = 69
   and n2.navigatable = 0 
 group by i_n2.nounID

[Confusion]

Ravenof schreef op zaterdag 24 mei 2008 @ 17:29:
De table is voorzien van indexen.

Welke indexen?

[oogapp0ltje]

T.T. schreef op zaterdag 24 mei 2008 @ 17:37:
lijkt een beetje op full-text search? Daar blinkt MySQL nooit zo erg in uit, maar is wel genoeg documentatie over te vinden

Ik vergat nog te vermelden dat het idd om MySQL gaat. Het is overigens geen full-text search, het gaat om een db met woorden, geen volledige tekst e.d.

T.T. schreef op zaterdag 24 mei 2008 @ 17:37:
Je kan het ook over een geheel andere (niet-DB) boeg gooien, maar dat wil je wellicht niet. Mocht je dat wel leuk vinden, probeer dan eens Solr of Sphinx (MySql gebaseerd)

Goed punt, Solr werkt snel en kan een mooie gefaceteerde index opbouwen, echter maak ik het toch liever zelf

T.T. schreef op zaterdag 24 mei 2008 @ 17:37:ik zou moeten toevoegen dat een lading subqueries, joins en group-by's nooit helpt. Simpele queries zijn sneller, dus probeer je probleem op te lossen waarbij je dat vermijdt. Dat betekent dus dingen zoals een inverted index vooraf opbouwen (of laten opbouwen)

Dat is precies waar ik ook al aan zat te denken. Een vooraf opgebouwde index m.b.v. cartesian product queries of iets dergelijks. Enige nadeel is wel dat dit wel tijd kost en lastig is met updates/deletes/inserts. Zijn er verder geen manieren om bovenstaande query te optimaliseren?

[Voutloos]

Ravenof schreef op zaterdag 24 mei 2008 @ 18:14:
Zijn er verder geen manieren om bovenstaande query te optimaliseren?

Er is al naar meer informatie gevraagd, omdat we anders niet ver komen zonder glazen bol. Geef dus mysql versie nummer, tabel structuren inc indexen en explain output. Zonder deze gegevens kan je uberhaupt niet beginnen met optimaliseren...