[sql] in() omzetten naar joins

[link0007]

voor een site die ik aan het maken ben, heb ik een lekker vettige zoekfunctie. De site bestaat uit "scenes", welke vergelijkbaar zijn met youtube filmpjes/forum topics qua idee. Met de zoekfunctie kan je in de scenes zoeken (scene_title, scene_description, scene_author), maar ook in de tags en de comments (alles optioneel).

Nu begin ik te merken dat met het met comments best traag gaat. de query ziet er dan zo uit:

SQL:

SELECT * FROM pbox_scenes
WHERE
    scene_group != 0
    AND 
    MATCH (scene_description) AGAINST ('test')
    OR
            scene_id 
        IN
        (
            SELECT comment_scene_id FROM pbox_comments
            WHERE 
                comment_alive=1
                AND
                MATCH ( comment_content ) 
                AGAINST ( 
                    'test'  
                        )
        )
ORDER BY scene_datetime DESC 
LIMIT 0,30

Deze query duurt +-14 seconden(!!) (17k scenes, 40k comments)

Het gekke is dat de subquery zelf maar 0.01 seconde kost (oid), dus het probleem zit hem in het cross-checken van die tabellen. Van wat ik gelezen heb op het internet is het beter om joins te gebruiken hiervoor.
Nu denk ik dat deze best omgeschreven kunnen worden naar joins, maar ik kom er maar niet uit.
Omdat ik resultaten wil op een "of/of" basis, moet ik kijken naar full joins? (meen ik te begrijpen)

Voor dit geval heb ik veel geprutst, en uiteindelijk kwam dit eruit:

SQL:

SELECT * FROM 
(
    SELECT * FROM  pbox_scenes
    WHERE
    MATCH ( scene_description ) AGAINST ( 'test' )
)
full JOIN
(
    SELECT distinct comment_scene_id FROM pbox_comments
    WHERE
    MATCH ( comment_content ) AGAINST ( 'test' )    
) as comments
on scene_id=comments.comment_scene_id 

Maar die werkt niet helemaal goed, want in plaats van 273 resultaten krijg ik er maar 15 terug

Daarbij zit ik met een boel subqueries en criteria te prutsen, dus ik krijg het al helemaal niet voor elkaar om een query als deze om te schrijven naar joins:

SQL:

SELECT * FROM pbox_scenes
WHERE
scene_group != 0
AND 
(
    MATCH (scene_title) AGAINST ('test')
    OR
    MATCH (scene_description) AGAINST ('test')
    OR
    scene_id 
    IN
    (
        SELECT comment_scene_id FROM pbox_comments
        WHERE 
            comment_alive=1
            AND
            MATCH (comment_content) AGAINST ('test')
    )
    OR
    scene_id 
    IN
    (
        SELECT tag_scene_id FROM pbox_scene_tags 
        WHERE 
            MATCH (tag_name) AGAINST ('test')
    )
)
ORDER BY scene_datetime DESC 
LIMIT 0,30

kan iemand hier mij helpen met het omschrijven/optimalizeren van deze query?

[NMe]

Je hoeft dit toch niet per se in één query te doen?

Als je dat toch wil: kun je niks met een union-constructie?

[link0007]

niet in een query? je bedoelt joinen via php?

En die unions willen niet lukken (heb ze net via phpmyadmin uitgeprobeert, maar die query was na een minuut nog niet verwerkt)

SQL:

SELECT * FROM pbox_scenes
WHERE
(
    scene_group != 0
    AND
    (
        MATCH (scene_title) AGAINST ('test')
        OR
        MATCH (scene_description) AGAINST ('test')
    )
)
UNION
SELECT FROM pbox_comments
WHERE
(
    comment_alive=1
    AND
    MATCH (comment_content) AGAINST ('test')
)
UNION
SELECT FROM pbox_scene_tags
WHERE
(
    MATCH (tag_name) AGAINST ('test')
)
ORDER BY scene_datetime DESC 
LIMIT 0,30

Doe ik hier iets fout waardoor het niet werkt? Snap niet helemaal wat de bedoeling is van die unions wanneer je niks uit die twee "externe" tabellen wilt opvragen

[MBV]

UNION voegt 2 resultaten samen:

SQL:

(
  SELECT "eersteresulaat" as value
)
UNION
(
  SELECT "tweederesultaat"  as value
)

zou 2 regels terug moeten geven.

Je zal dus 2 queries moeten schrijven die resultaten uit pbox_scenes teruggeven (in hetzelfde formaat, dus pbox_scenes.*!) met verschillende condities. De eerste klopt, de tweede moet je een simpele INNER JOIN tussen pbox_scenes en pbox_comments doen.

[cariolive23]

scene_id en comment_scene_id geven de relatie aan?

SQL:

SELECT 
  * 
FROM 
  pbox_scenes
    LEFT JOIN pbox_comments ON pbox_scenes.scene_id = pbox_comments.comment_scene_id
WHERE 
    pbox_scenes.scene_group <> 0 
AND  (
      MATCH (pbox_scenes.scene_description) AGAINST ('test') 
  OR (
        pbox_comments.comment_alive = 1 
      AND 
        MATCH ( pbox_comments.comment_content )  AGAINST ('test') 
        )
    )
  ) 
ORDER BY 
  pbox_scenes.scene_datetime DESC  
LIMIT 30
OFFSET 0

Wellicht moet de vergelijking op de tabel pbox_comments nog even naar de JOIN worden verplaatst. Test het even en je weet het.

Het * mag je nog even vervangen door de juiste tabel- en kolomnamen, dat zorgt voor véél meer duidelijkheid en voorkomt problemen met dubbele namen en dus bugs.

edit:

Even [code=sql][/code] van gemaakt, kleurtjes staan wat leuker

[ Voor 5% gewijzigd door Woy op 19-03-2009 13:44 . Reden: Staan er nu wel genoeg haakjes? ) ( cariolive23 ) ]

[MBV]

Soms hoop je dat de TS iets leert, en geef je een omschrijving van de oplossing in de hoop dat hij het snapt

offtopic:
als je [norml] gebruikt moet je hem ook sluiten, en met \[ kan je zo'n tag ook escapen

[ Voor 72% gewijzigd door MBV op 19-03-2009 14:54 ]

[Woy]

MBV schreef op donderdag 19 maart 2009 @ 13:34:

offtopic:
als je [norml] gebruikt moet je hem ook sluiten, en met \[ kan je zo'n tag ook escapen

offtopic:
Wat

[MBV]

Woy schreef op donderdag 19 maart 2009 @ 13:45:
[...]

offtopic:
Wat

modbreaken om een foutje van een gebruiker in tags te fixen Heb je niks beters te doen
Ik val in herhaling: Soms hoop je dat de TS iets leert, en geef je een omschrijving van de oplossing in de hoop dat hij het snapt (en het de volgende keer zelf goed doet, zonder modbreaks)

[ Voor 10% gewijzigd door MBV op 19-03-2009 17:31 ]

[EfBe]

Wat voor database gebruik je? Want een beetje database optimaliseert IN predicates veelal naar dezelfde execution plans als joins. MySql veelal niet, want MySql is... nou ja... niet echt capabel, en is sneller in joins. Overigens denk ik dat de meeste tijd in de MATCH gaat zitten dus het is lood om oud ijzer.

[justmental]

Het is iig. een optimizer probleem.
Ik vermoed dat de subquery voor elke rij van de hoofdquery uitgevoerd wordt door de OR in de hoofdquery.
Je kunt de optimizer misschien een ander pad op sturen door deze OR om te schijven naar een union all.

[link0007]

het werkt met die joins prima

Maakt het ook stukken makkelijker om dynamisch queries te bouwen met php

Bedankt voor de hulp... Lijkt een beetje op een code-request, ik weet het, maar joins zijn moeilijk uit te leggen zonder voorbeeld denk ik En ja, die * voor kolomselectie heb ik er maar even snel van gemaakt, zodat de query wat compacter blijft. Hele lappen code zijn nu ook niet nodig.


Echt bedankt! Die joins snap ik nu ook eindelijk. Is toch een aparte manier van naar het probleem kijken, dat gedoe met databases

Query tijden zijn nu <1seconde

[MBV]

Gelukkig ben je geholpen. Snap je het nu ook echt? Dit is namelijk echt werk voor de beginnerscursus SQL

[Woy]

Als je problemen met joins hebt kun je natuurlijk ook even hier kijken: Hoe werken joins

[link0007]

ja haha, best basis inderdaad (je schaamt je toch weer altijd, als je hulp moet vragen bij zoiets simpels ). Ik ben in principe geen slechte programmeur, maar ik zit gewoon veel meer in PHP/whatever te rommelen, dan in SQL. Meestal schrijf je ff een query (vaak nogal simpel ook), en ga je daarmee aan de haal met je code. Dan komt het wel eens voor dat je simpele zaken niet weet in SQL

Ik begrijp ze nu wel een stuk beter!

Bedankt

[MBV]

Vergeet ook niet dat joins in MySQL een stuk sneller gaan dan ze handmatig in PHP doen

* MBV heeft nogal wat code gezien waarbij in een for-loopje de relaties in aparte queries werden opgehaald

PHP:

<?php

$resultArray = $db->getArray("SELECT * FROM x WHERE name LIKE ".$db->escape($something));
foreach($resultArray as $result) {
  //...
  $y = $db->getValue("SELECT something FROM y WHERE id = ".$result['id']);
  //...
}

Waarom?

[link0007]

haha, doe ik ook nog wel eens als ik een snel concept moet maken.. Bijvoorbeeld laatst moest ik van m'n oude site scenes en comment converten naar nieuw formaat. Dus toen loopte ik gewoon door de scenes, converte die, en haalde dan per scene de comments op, en converte die

Misschien niet het slimste, maar vooral als je snel en bug-free iets wilt doen, is zoiets toch wel verleidelijk.

Maar inderdaad, ik probeer altijd onder 10 queries te blijven. Soms is het niet anders, en moet je er ff overheen. Maar als je opeens 100 queries doet voor een pagina, moet je je toch gaan afvragen of dat niet beter kan

Maximum voor mijn site is nu 16, welke alleen gehaalt word wanneer je een scene upload

Geen zorgen hoor, de meeste foute gewoontes ken ik inmiddels wel!

[Verwijderd]

Want dat is sneller dan een JOIN query schrijven?

[MBV]

@Sabesniat: sneller misschien niet, maar als je wilt converten is het makkelijker = minder complex om een for-loop te schrijven.

Ik heb wel pagina's gezien die bij N items om te tonen N^2 queries nodig hadden...