[MySQL/PHP]Zoeken naar meerdere woorden optimaliseren

[Verwijderd]

Beste tweakers

Ik heb een script, maar ik ben er niet echt tevreden over...
Het moet sneller/beter kunnen, maar ik zie/snap het niet helemaal.

PHP:

//Voor elk zoekwoord apart een query aanroepen.
for($i=0; $i<$aantalKeyWords; $i++)
{
    //Alle pagina ID's uit de db halen die overeen komen met het zoekwoord.
    $query = "SELECT pages.Id AS id FROM keywords, pagekeywords, pages WHERE keywords.Word LIKE '%$arrayKeyWords[$i]%' AND pagekeywords.WordId=keywords.Id AND pages.Id=pagekeywords.PageId";

    $zoekWordId = mysql_query($query) or die ("ack! query failed: "
    ."<li>errorno=".mysql_errno()
    ."<li>error=".mysql_error()
    ."<li>query=".$query);

    //unieke nieuwe array maken voor vergelijking.
    $$aantalKeyWords[$i] = array();

    while($row = mysql_fetch_array($zoekWordId))
    {
        //alle pagina id's in een array stoppen.
        array_push($$aantalKeyWords[$i], "$row[id]");
    }

    //alle array's samenvoegen.
    array_push($arraySchoon, $$aantalKeyWords[$i]);
}

//de eerste array pakken om te gaan vergelijken.
$resultId = $arraySchoon[0];

for($i=1; $i<count($arraySchoon); $i++)
{
    //elke array word vergeleken en alleen de pagina id's die in beide voorkomen worden opgeslagen.
    $resultId = array_intersect($resultId, $arraySchoon[$i]);
}

//alle dubbele waardes worden eruit gegooid.
$resultId2 = array_values(array_unique($resultId));
$AantalGevonden = count($resultId2);
//String maken voor een volgende query.
$stringId = implode($resultId2, "'||Id='");

Ik dacht het slim aan te pakken door een array te gebruiken in de query, maar ik snap het gebruik van een array in een query niet helemaal.

Aangepast script:

PHP:

//Alle pagina ID's uit de db halen die overeen komen met de zoekwoorden uit de array.
$query = "SELECT pages.Id AS id FROM keywords, pagekeywords, pages WHERE keywords.Word LIKE '%$arrayKeyWords%' AND pagekeywords.WordId=keywords.Id AND pages.Id=pagekeywords.PageId";

$zoekWordId = mysql_query($query) or die ("ack! query failed: "
."<li>errorno=".mysql_errno()
."<li>error=".mysql_error()
."<li>query=".$query);

$resultId = array();

while($row = mysql_fetch_array($zoekWordId))
{
    //alle pagina id's in een array stoppen.
    array_push($resultId, "$row[id]");
}

//alle dubbele waardes worden eruit gegooid.
$resultId2 = array_values(array_unique($resultId));
$AantalGevonden = count($resultId2);
$stringId = implode($resultId2, "'||Id='");

Maar met het eerste script krijg ik veel meer resultaten dan met het tweede script.

[chris]

het is me niet heel erg duidelijk wat je precies wil, maar kan je niet die array exploden en die keywords met '%kw1%' OR '%kw2%' (enz.) gaan selecteren?

[Verwijderd]

Het gaat om een vrij grote database. Als je op "groot" zoekt ofzo dan krijg je 2963 resultaten. Dit wil ik beperken, sowieso om MySQL en de server te ontlasten.

De eerste manier die ik boven beschrijf is een beetje omslachtig. Voor elk zoekwoord gebruik ik een apparte query (LIKE '%$zoekwoord%') en vul ik een array die later weer vergeleken wordt met de andere array.

De tweede manier wordt er gebruik gemaakt van 1 query maar met een array (LIKE '%$array%').

Waarom ik het ook zo wil. Ik wil een maximaal resultaat van 100 uit de db halen (LIMIT 100) en dat kan als je 1 query gebruikt. Als je de eerste manier gebruikt, dan kan je geen limit 100 gebruiken want dan kloppen de resultaten niet meer (Omdat dit per woord gaat dan en niet een limit 100 van het gehele resultaat).

Ik hoop dat dit zo wat duidelijker wordt

[Banpei]

Heb je wel eens de MySQL Full Text Search bekeken? Eenvoudiger om aan te sturen en de woorden worden automatisch geindexeerd op relevantie.

[Verwijderd]

Laten we anders ff bij het begin beginnen

Wanneer kan je een array gebruiken in een SELECT query?
Wat is het verschil tussen de volgende query's?

PHP:

//mogelijkheid 1
$array = array("a", "b", "c");
$query = "SELECT id FROM tabel WHERE letter = '$array'";

//mogelijkheid 2
$array = array("a", "b", "c");

for($i=0; $i<count($array); $i++)
{
  $query = "SELECT id FROM tabel WHERE letter = '$array[$i]'";
}

[gorgi_19]

De eerste geeft een foutmelding
De tweede zal waarschijnlijk alleen het resultaat met de selectquery van c bevatten, aangezien de eerdere steeds overschreven worden.

[Verwijderd]

Banpei schreef op 08 januari 2004 @ 11:18:
Heb je wel eens de MySQL Full Text Search bekeken? Eenvoudiger om aan te sturen en de woorden worden automatisch geindexeerd op relevantie.

Jawel, maar mijn database structuur zit anders in elkaar (werkt perfect) en ik hoef zo niet op relevantie te sorteren (gaat later opdatum).

Het enige wat ik wil is dat alle zoekwoorden overeen komen.

[Verwijderd]

gorgi_19 schreef op 08 januari 2004 @ 11:20:
De eerste geeft een foutmelding
De tweede zal waarschijnlijk alleen het resultaat met de selectquery van c bevatten, aangezien de eerdere steeds overschreven worden.

Ik krijg geen foutmelding als ik een array gebruik
Een da code heb ik zo ff ingeklopt. Het gaat puur om de query's, het verwerken van het resultaat staat er niet bij

[gorgi_19]

Verwijderd schreef op 08 januari 2004 @ 11:23:
[...]

Ik krijg geen foutmelding als ik een array gebruik

Dan ben ik heel benieuwd hoe de raw output van je query er uit ziet.. Best kans dat hij de array als string ziet, ipv individuele items.

[ Voor 13% gewijzigd door gorgi_19 op 08-01-2004 11:25 ]

[Verwijderd]

gorgi_19 schreef op 08 januari 2004 @ 11:24:
[...]

Dan ben ik heel benieuwd hoe de raw output van je query er uit ziet.. Best kans dat hij de array als string ziet, ipv individuele items.

Dat zo dus ook mogelijk zijn.
Het is voor onduidlijk, sommige zeggen dat het kan en sommige zeggen dat het niet mogelijk is. Maar hoe en wat

[gorgi_19]

Echo je query eens; ik denk dat dat wel eea verklaard.

En je voorbeelden zijn compleet verschillend tov je probleem. Bij je abc-voorbeeld zoek je een exacte match, in je eigenlijke probleem zoek je naar een woord in een kolom.

Ik ken PHP niet goed; maar het zal iets zijn als

PHP:

$query = "SELECT id FROM tabel WHERE letter IN(".$array.")";

In je hoofdvoorbeeld moet je zoeken naar:

SQL:

LIKE '%keyword1%' AND LIKE '%keyword2%' 

[ Voor 109% gewijzigd door gorgi_19 op 08-01-2004 11:33 ]

[ACM]

Je kan dan ook in principe geen array's gebruiken in queries, sowieso zal dat er in resulteren dat je dan een query krijgt die een of meer van de woorden bevat, ipv alle. (Met de IN-operator kom je een aardig eind).

Wat je es kan proberen is de volgende twee:

SQL:

SELECT
  page.id
FROM
  page,
  word w1,
  word w2,
...,
  wordpage wp1,
  wordpage wp2,
....,
WHERE 
w1.word = '$word1' AND w1.id = wp1.wordid AND page.id = wp1.pageid
AND
w2.word = '$word2' AND w2.id = wp2.wordid AND page.id = wp2.pageid
AND ...

Deze wordt met elk woord dat je meer zoekt een behoorlijke hoeveelheid trager (itt tot je huidige oplossing). OR-functionaliteit even snel maken als deze AND-functionaliteit is geloof ik min of meer een hel

Een andere oplossing:

SQL:

SELECT
   page.id,
   count(wordpage.*) as count
FROM
  page,
  word,
  wordpage
WHERE
  word IN ('$word1', '$word2', ...)
  -- Als je perse like wilt, kan je beter eerst even alle wordids opsnorren denk ik
  AND
  word.id = wordpage.wordid
  AND
  page.id = wordpage.pageid
GROUP BY 
  page.id
HAVING
  count = $wordcount

Deze zal wat stabieler in performance zijn en is ook eenvoudig aan te passen voor OR-queries (een left join van die wordpage-page relatie maken en testen op de count > 1, wat nog niet echt ideaal is met veel documenten, magoed)

[edit]
Owja, als je perse die like nodig hebt kan het ook zo:
(word.word LIKE '$word1' OR word.word LIKE '$word2' ...)
AND

[ Voor 5% gewijzigd door ACM op 08-01-2004 11:58 ]

[Verwijderd]

gorgi_19 schreef op 08 januari 2004 @ 11:30:
Echo je query eens; ik denk dat dat wel eea verklaard.

En je voorbeelden zijn compleet verschillend tov je probleem. Bij je abc-voorbeeld zoek je een exacte match, in je eigenlijke probleem zoek je naar een woord in een kolom.

Ik ken PHP niet goed; maar het zal iets zijn als

PHP:

$query = "SELECT id FROM tabel WHERE letter IN(".$array.")";

In je hoofdvoorbeeld moet je zoeken naar:

SQL:

LIKE '%keyword1%' AND LIKE '%keyword2%' 

Je hebt gelijk

De array wordt omgezet naar de string "array":
SELECT pages.Id AS id FROM keywords, pagekeywords, pages WHERE keywords.Word LIKE '%Array%' AND pagekeywords.WordId=keywords.Id AND pages.Id=pagekeywords.PageId

Maar is dit nu een conclusie dat je geen array kan gebruiken bij MySQL?

[ACM]

php-arrays en database-arrays (mysql heeft ze niet, magoed) moet je natuurlijk wel op een geldige manier op elkaar mappen... Niet domweg je array als een soort tekst-string ertussen kwakken en hopen dat er een geldige query uitrolt terwijl je zelf allang had kunnen beredeneren dat dat niet mogelijk is.

Arrays zijn overigens geen onderdeel van de sql-standaard en worden maar weinig ondersteund, postgresql kent ze en kan ze zelfs in een IN gebruiken (maar als je toch een lijst hebt kan je net zo goed zelf die in-lijst opbouwen). MySQL kent ze niet, maar daar kan je nog steeds je in gebruiken

[Verwijderd]

ACM schreef op 08 januari 2004 @ 11:51:
Niet domweg je array als een soort tekst-string ertussen kwakken en hopen dat er een geldige query uitrolt terwijl je zelf allang had kunnen beredeneren dat dat niet mogelijk is.

Tja.. soms hoop je op iets
Maar je hebt gelijk.
IIG weet ik het nu zeker.

[dream0r]

Zelf gebruik ik dit in een bepaalde situatie waar er gezocht moet worden of een woord voorkomt. Je kan $search dan gebruiken in je where. Ook moet je even de velden aanpassen.

PHP:

// Zoeken
$search = NULL;

if (isset($_POST["keyword"]))
{
    $keywords = explode(" ", $_POST["keyword"]);
    $search = "WHERE ";
    $and = NULL;
    
    for ($i = 0; $i < count($keywords); $i++)
    {           
        $search .= " ".$and." (veld LIKE '%".$keywords[$i]."%' OR veld2 LIKE '%".$keywords[$i]."%') ";
        $and = "AND";
    }
}

[Verwijderd]

Ja idd ben ik nu ook maar aan het doen