[MySQL] Joins + FullText == traag?

[mathijs92]

Ik heb de volgende query gemaakt:

SQL:

SELECT *
FROM 
knhs_cpv_pe as pe
RIGHT JOIN
knhs_cpv_co as co
ON
co.persoonsnummer = pe.persoonsnummer 
LEFT JOIN
knhs_cpv_di as di
ON
co.diernummer = di.diernummer
WHERE  MATCH(pe.voorletters, pe.voornaam, pe.tussenvoegsels, pe.achternaam) AGAINST('Jansen')
GROUP BY co.combinatienummer

Deze query neemt ruim de tijd, ergens rond de 2 seconden.

Als ik de volgende query uitvoer, gaat het veel sneller (0,118 sec):

SQL:

SELECT * 
FROM knhs_cpv_pe AS pe
RIGHT JOIN knhs_cpv_co AS co ON co.persoonsnummer = pe.persoonsnummer
LEFT JOIN knhs_cpv_di AS di ON co.diernummer = di.diernummer
WHERE pe.achternaam =  'Jansen'
GROUP BY co.combinatienummer

Het verschil is dus dat er hier geen gebruik wordt gemaakt van de FullText index. Het probleem zit daar echter ook niet.
Als ik de join weghaal, en dan de fulltext gebruik om te zoeken, wordt de query uitgevoerd in 0,0006 seconden. De query is dan:

SQL:

SELECT *
FROM 
knhs_cpv_pe as pe
WHERE  MATCH(pe.voorletters, pe.voornaam, pe.tussenvoegsels, pe.achternaam) AGAINST('Adelaars')

Een explain van de eerste query staat op: http://i56.tinypic.com/ibg8j4.png
Wie weet waar dit aan zou kunnen liggen? ik heb me natuurlijk al helemaal rot gezocht, maar dat heeft niet mogen baten.

[ Voor 3% gewijzigd door mathijs92 op 04-10-2010 15:52 ]

[ajakkes]

De laatste query uitvoeren met 'Jansen' maakt de query niet veel langzamer?

Verder zie ik nog geen aanwijzingen.

Je topic-titel zou je database-type tussen block-quotes mogen bevatten

Edit:
Oh, ik bedenk me nu. Een ge-joinde tabel bevat natuurlijk VEEL meer velden dan een gewone tabel. Hij zal al deze "dubbele" velden ook gaan nazoeken, of niet?

Misschien dat er een andere mogelijkheid is om eerst de where uit te voeren en daarna pas de tabellen te joinen.

[ Voor 42% gewijzigd door ajakkes op 04-10-2010 16:01 ]

[NMe]

Using temporary, using filesort. En daar fulltext op. Lijkt me niet optimaal nee.

Sowieso: Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk? Je group by klopt voor geen meter en de enige reden dat het toch werkt is dat je MySQL gebruikt.

[Hydra]

Ik weet niet welke DB je gebruikt, maar veel systemen 'zien' in dit soort gevallen de full text index niet als index. Ze bouwen eerst dus een cartesisch product en gaan daarna matchen. Volgens mij kun je in Oracle via hints aangeven dat 'ie het andersom moet doen, maar dit is dus nogal afhankelijk van je RDBMS.

offtopic:
Spuit elf: eerst refreshen, dan reageren...

[ Voor 8% gewijzigd door Hydra op 04-10-2010 16:11 ]

[NMe]

Probeer deze eens?

SQL:

EXPLAIN SELECT *
FROM 
knhs_cpv_pe as pe
RIGHT JOIN
knhs_cpv_co as co
ON
co.persoonsnummer = pe.persoonsnummer AND MATCH(pe.voorletters, pe.voornaam, pe.tussenvoegsels, pe.achternaam) AGAINST('Jansen')
LEFT JOIN
knhs_cpv_di as di
ON
co.diernummer = di.diernummer
GROUP BY co.combinatienummer

Geen idee of dit mag, maar dit zou er wel voor moeten zorgen dat de index gebruikt wordt vóór de join, als het werkt. Mocht het niet werken op deze manier: subquery.

edit:

Overigens heb ik je group by-fout laten staan, die mag je zelf verbeteren.

[ Voor 13% gewijzigd door NMe op 04-10-2010 16:13 ]

[mathijs92]

NMe schreef op maandag 04 oktober 2010 @ 16:12:
Probeer deze eens?

SQL:

EXPLAIN SELECT *
FROM 
knhs_cpv_pe as pe
RIGHT JOIN
knhs_cpv_co as co
ON
co.persoonsnummer = pe.persoonsnummer AND MATCH(pe.voorletters, pe.voornaam, pe.tussenvoegsels, pe.achternaam) AGAINST('Jansen')
LEFT JOIN
knhs_cpv_di as di
ON
co.diernummer = di.diernummer
GROUP BY co.combinatienummer

Geen idee of dit mag, maar dit zou er wel voor moeten zorgen dat de index gebruikt wordt vóór de join, als het werkt. Mocht het niet werken op deze manier: subquery.

edit:

Overigens heb ik je group by-fout laten staan, die mag je zelf verbeteren.

Dat gaat hem niet worden, ik krijg (ook zonder group by) 2,238,707 records, terwijl er maar 70.000 in de tabel staan
Als ik de group by (die inderdaad fout is ja) weghaal, wordt de query een stuk sneller. Hij duurt dan nog 0,45 sec. Als ik die explain krijg ik hetzelfde resultaat, maar dan zonder: Using temporary, using filesort.
Zou dit nog sneller kunnen, of wordt dat lastig?

[NMe]

mathijs92 schreef op maandag 04 oktober 2010 @ 16:40:
[...]

Dat gaat hem niet worden, ik krijg (ook zonder group by) 2,238,707 records, terwijl er maar 70.000 in de tabel staan

Dat zou wel eens door die dubbele outer join kunnen komen. Je wil hoe dan ook je fulltext index gebruiken vóór je join en niet erna, want anders wordt filesort gebruikt.

Als ik de group by (die inderdaad fout is ja) weghaal, wordt de query een stuk sneller. Hij duurt dan nog 0,45 sec. Als ik die explain krijg ik hetzelfde resultaat, maar dan zonder: Using temporary, using filesort.

Als je group by weghaalt uit mijn query, of uit jouw originele query? Als je hem weggehaald hebt uit mijn query dan zou dat wel eens zo ongeveer het snelste zijn dat je hem gaat krijgen zonder caching, als je hem weggehaald hebt uit jouw query, dan zou het sneller moeten kunnen door dus inderdaad die index vóór de join te gebruiken.

[mathijs92]

NMe schreef op maandag 04 oktober 2010 @ 16:53:
Als je group by weghaalt uit mijn query, of uit jouw originele query? Als je hem weggehaald hebt uit mijn query dan zou dat wel eens zo ongeveer het snelste zijn dat je hem gaat krijgen zonder caching, als je hem weggehaald hebt uit jouw query, dan zou het sneller moeten kunnen door dus inderdaad die index vóór de join te gebruiken.

Uit mijn originele query. Kan ik die nog verder optimaliseren (evt tips)? Dan ga ik daarna de GROUP BY even goed in elkaar steken

[NMe]

mathijs92 schreef op maandag 04 oktober 2010 @ 17:00:
[...]

Uit mijn originele query. Kan ik die nog verder optimaliseren (evt tips)? Dan ga ik daarna de GROUP BY even goed in elkaar steken

Doe de match eens in een subquery waar je op joint, zoals ik hierboven zei?

[mathijs92]

SQL:

SELECT * 
FROM  
knhs_cpv_co as co 
LEFT JOIN 
knhs_cpv_di as di 
ON 
co.diernummer = di.diernummer 
WHERE  co.persoonsnummer IN (SELECT persoonsnummer FROM knhs_cpv_pe WHERE MATCH(knhs_cpv_pe.voorletters, knhs_cpv_pe.voornaam, knhs_cpv_pe.tussenvoegsels, knhs_cpv_pe.achternaam) AGAINST('Jansen'))

Ik kom hier op uit, maar dat duurt langer (6 seconden, ook zonder group by). Doe ik het verkeerd of is dat gewoon trager?

[ Voor 5% gewijzigd door mathijs92 op 04-10-2010 17:19 ]

[NMe]

SQL:

EXPLAIN SELECT *
FROM knhs_cpv_pe as pe
RIGHT JOIN knhs_cpv_co as co
  ON co.persoonsnummer = pe.persoonsnummer AND co.persoonsnummer IN (
    SELECT persoonsnummer
    FROM knhs_cpv_pe
    WHERE MATCH(knhs_cpv_pe.voorletters, knhs_cpv_pe.voornaam, knhs_cpv_pe.tussenvoegsels, knhs_cpv_pe.achternaam) AGAINST('Jansen')
  )
LEFT JOIN knhs_cpv_di as di
  ON co.diernummer = di.diernummer

Zo dan?

En laat die group by nu eens helemaal weg of gebruik hem goed. Op deze manier heb je er niks aan.

[ Voor 11% gewijzigd door NMe op 04-10-2010 17:17 ]

[ZaZ]

Deze aan het begin van je sessie uitvoeren

SQL:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

Helpt je iets bij het schrijven van je group by.

[ Voor 3% gewijzigd door ZaZ op 04-10-2010 17:17 ]

[mathijs92]

NMe schreef op maandag 04 oktober 2010 @ 17:16:

SQL:

EXPLAIN SELECT *
FROM knhs_cpv_pe as pe
RIGHT JOIN knhs_cpv_co as co
  ON co.persoonsnummer = pe.persoonsnummer AND co.persoonsnummer IN (
    SELECT persoonsnummer
    FROM knhs_cpv_pe
    WHERE MATCH(knhs_cpv_pe.voorletters, knhs_cpv_pe.voornaam, knhs_cpv_pe.tussenvoegsels, knhs_cpv_pe.achternaam) AGAINST('Jansen')
  )
LEFT JOIN knhs_cpv_di as di
  ON co.diernummer = di.diernummer

Zo dan?

En laat die group by nu eens helemaal weg of gebruik hem goed. Op deze manier heb je er niks aan.

Die is snel, maar er worden wel 2 miljoen rijen bij verzonnen. (PHPMyAdmin vind dat niet fijn)

[NMe]

Nogmaals: je hebt een dubbele outer join. Volgens mij moeten dat gewoon inner joins zijn. Overigens is deze query om dezelfde reden snel dat mijn eerdere uitgeschreven query snel was (maar teveel resultaat gaf). Je zou voor eenvoud waarschijnlijk liever mijn eerdere query willen gebruiken.

[ Voor 55% gewijzigd door NMe op 04-10-2010 17:38 ]

[Wolfboy]

ZaZ schreef op maandag 04 oktober 2010 @ 17:17:
Deze aan het begin van je sessie uitvoeren

SQL:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

Helpt je iets bij het schrijven van je group by.

Of gewoon direct "ANSI,TRADITIONAL"

@TS: begin eens met de match/against in de join clause te plaatsen. Dat zou al wat moeten helpen voor de snelheid.

[NMe]

Wolfboy schreef op maandag 04 oktober 2010 @ 17:36:
[...]

@TS: begin eens met de match/against in de join clause te plaatsen. Dat zou al wat moeten helpen voor de snelheid.

Mijn posts gemist?

[mathijs92]

Dit is al een stuk beter:

SQL:

EXPLAIN SELECT * 
FROM  
knhs_cpv_pe as pe
INNER JOIN 
knhs_cpv_co as co 
ON 
co.persoonsnummer = pe.persoonsnummer AND (MATCH(pe.voorletters, pe.voornaam, pe.tussenvoegsels, pe.achternaam) AGAINST('Jansen') )
INNER JOIN 
knhs_cpv_di as di 
ON 
co.diernummer = di.diernummer 

Volgens PHPMyAdmin duurt deze query slechts 0.0063 seconden. Het lijkt me dat deze niet heel veel sneller gaat worden. Bedankt!
Dan ga ik nu die GROUP BY maar eens fixen

[ Voor 5% gewijzigd door mathijs92 op 04-10-2010 17:52 ]

[NMe]

Begrijp je ook waarom het sneller is dan jouw variant? Want da's wel een belangrijk stukje info.

[Wolfboy]

NMe schreef op maandag 04 oktober 2010 @ 17:39:
[...]

Mijn posts gemist?

offtopic:
Nee, maar misschien dat er wel geluisterd wordt als meer mensen het zeggen

Prima query werk overigens