[sql] probleem met joinen van fulltext search

[Zynth]

Ik heb twee tabellen van mijn forum. Een tabel met post_texts en een tabel met topics.
Nu wil ik een fulltext search doen op die 2 tabellen, omdat een gebruiker moet kunnen zoeken in zowel de topic_titels als de post_texts tegelijk.

Momenteel heb ik dat gesplitst:

code:

  $query_topics="SELECT topic_id, MATCH(topic_title) AGAINST ('$search') AS score FROM bb_topics HAVING score>0 ORDER BY score DESC";
  $query_posts="SELECT post_id, MATCH(post_text) AGAINST ('$search') AS score FROM bb_posts_text HAVING score>0 ORDER BY score DESC";

Maar dat is niet echt handig. Het liefst wil ik de resultaten in 1 sql-set,
met bijvoorbeeld de volgende kolommen:
Type {topic|post}, id, score

Matchen met bijvoorbeeld .... MATCH(A.topic_title, B.post_text) is blijkbaar niet toegestaan...

Left-joinen is ook niet nuttig, omdat je niet de 2 tabellen "naast elkaar" wilt zetten. Je wilt ze eigenlijk "onder elkaar" hebben. Wat je eigenlijk zou willen is een "nieuwe" tabel c.q. view maken met de kolommen:
Type {topic|post}, topic_id_OF_post_id, topic_title_OF_post_text
en daar dan de search op doen...

Iemand suggesties?

[Voutloos]

Twee MATCH AGAINST clauses gebruiken is ook een optie. Fulltext wil nog wel eens slecht presteren, dus je zou de diverse opties toch moeten testen voor wat betreft performance.

[Zynth]

Voutloos schreef op dinsdag 29 mei 2007 @ 12:40:
Twee MATCH AGAINST clauses gebruiken is ook een optie. Fulltext wil nog wel eens slecht presteren, dus je zou de diverse opties toch moeten testen voor wat betreft performance.

Dat dat een optie is begrijp ik ja, het zal zelfs wel moeten.
Maar waar het om gaat is; hoe krijg ik de resultaatset die ik wil hebben?
M.a.w. welke query-opbouw zou je dan voorstellen?

[Verwijderd]

Ik heb exact een dergelijke constructie gebouwd.

hier is de voorbeeld url

SQL:

select virtual.id, virtual.naam, virtual.info, virtual.coordinaten, virtual.doelgroep, virtual.provincie, virtual.plaats, virtual.postcode, max(virtual.total) as score from ( select i.id as id, i.naam as naam, i.info as info, i.coordinaten as coordinaten, i.doelgroep as doelgroep, i.provincie as provincie, i.plaats as plaats, i.postcode as postcode, match(i.naam, i.info, i.doelgroep, i.provincie, i.plaats, i.postcode) against ('${query}') as score, match(k.omschrijving) against ('${query}') as score_kenmerk, match(r.omschrijving) against ('${query}') as score_rubriek, (match(i.naam, i.info, i.doelgroep, i.provincie, i.plaats, i.postcode) against ('${query}') + match(k.omschrijving) against ('${query}') + match(r.omschrijving) against ('${query}')) as total from m_instellingen i left join m_kenmerken_instellingen ki on (i.id = ki.instellingId) left join m_rubrieken_instellingen ri on (i.id = ri.instellingId) left join m_kenmerken k on (k.id = ki.kenmerkId) left join m_rubrieken r on (r.id = ri.rubriekId) where ( match(i.naam, i.info, i.doelgroep, i.provincie, i.plaats, i.postcode) against ('${query}') or match(k.omschrijving) against('${query}') or match(r.omschrijving) against('${query}') ) ) as virtual %s group by id order by score DESC %s;

Een beetje slecht qua formatting, maar dat moet je maar ff fixen.

Ik heb een FullText index op
1. naam, i.info, i.doelgroep, i.provincie, i.plaats, i.postcode
2. k.omschrijving
3. r.omschrijving

Er zitten een aantal left joins op, er worden dus 3 scores opgeleverd die naar gelang kunnen worden gesorteerd (voor gewicht). Dit alles zit in een subselect waar de totale score wordt berekend om relevantie aan te kunnen geven.