[MySQL] Performance probleem buildin FULLTEXT

[bartvb]

Of ja, eigenlijk heeft het niet echt strikt iets met het fulltext verhaal te maken maar goed.

Gaat over de zoekfunctie van een forum, heb deze query:

code:

   SELECT
    p.post_id
   FROM 
    posts_text pt
    LEFT JOIN posts p ON pt.post_id = p.post_id
    LEFT JOIN topics t ON p.topic_id = t.topic_id
   WHERE 
    MATCH (pt.post_text) AGAINST ('test')
   GROUP BY t.topic_id
   ORDER BY p.post_time desc

Query is heftig ingekort, hoop irrelevante dingen zijn weggelaten (join met forums table, ophalen lastpost, dat soort spul).

Oh, database waar het om gaat bevat 274.000 posts en 25.000 topics. Posts zijn opgesplitst in een tabel met alleen postid en de text en een tabel met overige gegevens (poster, tijd, ip, topic, etc). Op de posttext en de titels zitten MySQL fulltext indices.

Topics:
Data 1.849 KB
Index 1.725 KB
total 3.574 KB

Posts_text:
Data 95.961 KB
Index 102.919 KB
Overhead 588 Byte
Effective 198.879 KB
total 198.880 KB

3MB en 200MB dus.

Bovenstaande query is uit te voeren in iets van een tiende seconde is dus wel acceptabel. Dit is dus het geval als er ALLEEN op de post text wordt gezocht.

Heel ander beeld krijgen we als we gaan zoeken op het topic_title:

code:

   SELECT
    p.post_id
   FROM 
    posts_text pt
    LEFT JOIN posts p ON pt.post_id = p.post_id
    LEFT JOIN topics t ON p.topic_id = t.topic_id
   WHERE 
    MATCH (t.topic_title) AGAINST ('test')
   GROUP BY t.topic_id
   ORDER BY p.post_time desc

Door de opbouw van het search script ziet de query er bijna exact hetzelfde uit. Probleem is alleen dat deze query 8 seconde(!) duurt! Niet fun dus. Ik gok dat er eerst een full join op de posts table wordt gedaan en daarna gaat MySQL pas kijken welke rows er nou eigenlijk interessant zijn.

Als ik een select doe als:

code:

   SELECT
    p.post_id
   FROM 
    topics t
    LEFT JOIN posts p ON t.topic_id = p.topic_id
    LEFT JOIN posts_text pt ON p.post_id = pt.post_id
   WHERE 
    MATCH (t.topic_title) AGAINST ('test')
   GROUP BY t.topic_id
   ORDER BY p.post_time desc
   LIMIT 200

Dan duurt de query maar een tiende seconde.

Probleem is dat ik ze natuurlijk wil combineren, dus iets als:

code:

MATCH (pt.post_text) AGAINST ('test')
OR
MATCH (t.topic_title) AGAINST ('test')

Alleen krijg je dan dus queries die een seconde of 8 duren. Dit moet toch sneller kunnen?? Heb alleen geen flauw idee hoe

Any thoughts?

edit:
[ /code] vergeten

[bartvb]

Hmm, niemand?
Had ik het dan toch in /38 moeten zetten?

[joepP]

Op dinsdag 30 oktober 2001 18:46 schreef bartvb het volgende:

code:

   SELECT
    p.post_id
   FROM 
    posts_text pt
    LEFT JOIN posts p ON pt.post_id = p.post_id
    LEFT JOIN topics t ON p.topic_id = t.topic_id
   WHERE 
    MATCH (pt.post_text) AGAINST ('test')
   GROUP BY t.topic_id
   ORDER BY p.post_time desc

Wat wil je hier nou precies?

Je selecteert eerst alle post_texts die 'test' bevatten. Daarna ga je ze koppelen (via posts) aan topics. Daarna groepeer je op topic, waardoor je nog maar 1 (ook nog willekeurige!!!) post per topic overhoudt die aan je zoekcriteria voldoet. En daar ga je dan op sorteren. Beetje rare manier van doen.

Wat je wel moet doen:
Selecteer alle post_texts die 'test' bevatten. Koppel ze aan een topic, en zorg dat je elk topic maar 1x terug krijgt (SELECT DISTINCT). Nu moet je voor die topics een nieuwe select doen, zodat je het tijdstip van de laatste reactie terugkrijgt. Dit kan op 2 manieren:

1) Voeg een extra veld 'last_reaction_time' toe aan je topics tabel. Niet netjes, wel snel.
2) Gebruik een subselect. Dit ondersteunt MySQL alleen niet, maar daar moet je met joins wel omheen kunnen werken. Dit mag je zelf doen, ik zal de query MET subselect geven

code:

   SELECT 
      t.topic_id, MAX(p.post_time) last_post
   FROM 
      posts p, topics t
   WHERE 
      t.topic_id = p.topic_id AND
      t.topic_id IN (
         SELECT DISTINCT t.topic_id
         FROM topics t, posts_p, posts_text pt
         WHERE
         MATCH (pt.post_text) AGAINST ('test') AND 
         pt.post_id = p.post_id AND
         p.topic_id = t.topic
      )
   GROUP BY t.topic_id
   ORDER BY last_post DESC

Ik heb je JOINS verplaatst naar de WHERE clausule, is een kwestie van smaak Een indexje op p.post_id+p.post_time lijkt me ook geen kwaad kunnen. Kheb niets getest, dus of het allemaal werkt kan ik je niet zeggen.

Suc6!

[bartvb]

Klopt dat het een willekeurige reactie is, maar dat is op zich niet zo'n ramp.. Gaat erom dat het op die manier in 1 query kan.

Maar enige oplossing lijkt dus het opsplitsen? Ook voor het eigenlijke probleem (performance)? Hmm.. Niet zo blij mee..

Aangezien de nieuwe versie van het forum steeds dichter bij komt (en daar een homemade fulltext search in zit) ga ik denk ik maar even wat knutselen zodat er 2 verschillende queries gebruikt worden, dit afhankelijk van het feit of je op de text of op text+title zoekt.

Toch bedankt voor de hulp

BTW nieuwe versie ga ik denk ik maar eens op PostGreSQL draaien, ik wordt langzamerhand een beetje gestoord van die beperkingen van MySQL

[joepP]

Op woensdag 31 oktober 2001 14:30 schreef bartvb het volgende:
Klopt dat het een willekeurige reactie is, maar dat is op zich niet zo'n ramp.. Gaat erom dat het op die manier in 1 query kan.

Volgens mij is dat -wel- een ramp, aangezien je hele sortering op laatste reactie de soep in loopt zo.

Maar enige oplossing lijkt dus het opsplitsen? Ook voor het eigenlijke probleem (performance)? Hmm.. Niet zo blij mee..

Nee, het kan best in 1 query. Als je je selectie + joins allemaal in je WHERE clausule zet, kan je die toch gewoon aan elkaar koppelen? Dus WHERE (selectie1) OR (selectie2).

BTW nieuwe versie ga ik denk ik maar eens op PostGreSQL draaien, ik wordt langzamerhand een beetje gestoord van die beperkingen van MySQL

Ik ken het probleem, vooral het gebrek aan subselects maakt je queries er vaak niet leesbaarder op

[bartvb]

Heb de search maar ff tijdelijk uitgeschakeld (goh, waar kennen we dat van ). Gisteren tot 4 uur 's nachts bezig geweest met het redden van de DB. Ding was o.a. in de soep gelopen door dat search verhaal dat de DB absurt lang gelocked kan houden (10 minuten enzo).. Niet fun. Hierdoor (en vooral door een stomme fout die ik zelf heb gemaakt ) 1 dag aan posts (stuk of 2300) kwijtgeraakt GRR!

Maar goed, eerst ff wat aan m'n tentamens doen en dan ga ik later nog wel kijken of ik dat search verhaal niet wat simpeler kan maken zodat ik volledige (en geoptimaliseerde) SELECTs in dat search script kan zetten. Grootste performance probleem dat ik nu heb is dat die SELECT nogal generiek is. Voor sommige zoekopdrachten is het slim om op 'posts' te gaan joinen, soms op 'posts_text' en soms op 'topics'.. Maar goed, ding staat uit dus in principe zijn mijn performance problemen uit de wereld M'n users zijn er alleen niet echt blij mee

[Rense Klinkenberg]

Kijk ook een naar de EXPLAIN functie van MySQL. Hiermee kan je exact kijken hoe MySQL de query uitvoert, zodat je ook kan zien hoe tabellen ge-joined worden.

[Apache]

Op woensdag 31 oktober 2001 14:54 schreef bartvb het volgende:
...dat de DB absurt lang gelocked kan houden (10 minuten enzo)...

InnoDB

k'ben er nu thuis ook mee aan't experimenteren ziet er zeker veelbelovend uit

[Killemov]

Volgens mij kun je die indexen in ieder geval wel weggooien.