[PHP4/MySQL] Zoekopdracht/-machine versnellen

[nemesis_]

Naar 3 modellen zoeken kost geen tijd (hooguit honderdsten/duizendsten van een seconde). Zoeken naar 500 modellen kost 10 seconden! ruim. Dit moet natuurlijk beter kunnen.
Zou er iemand naar kunnen kijken en een suggestie (het liefst natuurlijk een oplossing) geven waar het beter/sneller kan.
De zoekresultaten moeten op relevantie gerangschikt worden.
Ondanks dat ik de tijd meet, blijft die aangeven dat het hooguit 1 seconde duurt, maar meestal nog steeds maar een paar tienden.
Op de plaats van ############TRAAG############ gaat het waarschijnlijk "fout".
Een db-dump: db dump en dan pmtest.sql

PHP:

$starttime = microtime();
$sql    = "SELECT m1.model_id,
                title AS model_title,
                year_of_publication AS model_year_of_publication
            FROM models m1, user_roles ur, consulting_activities ca
                LEFT JOIN models_consulting_activities m2 ON m1.model_id = m2.model_id
                LEFT JOIN models_user_roles m3 ON m1.model_id = m3.model_id
            WHERE m3.user_role_id = ur.user_role_id
                AND m2.consulting_activity_id = ca.consulting_activity_id
                AND ur.name IN ('".$user_roles."')
                AND ca.name IN ('".$cons_acts."')
            GROUP BY m1.model_id
            ORDER BY m1.model_id";
$result = $db->query($sql);
$endtime = microtime();
$time = "Zoeken: ".(($endtime-$starttime > 0) ? ($endtime-$starttime) : "0.00")."<br />";
$totaal = ($endtime-$starttime);
$starttime = microtime();
$models = array();
############TRAAG############
if ($result->numRows() > 0) {
    $i = 0;
    while ($result->fetchInto($row)) {
        $models[$i]['model_id']                     = $row['model_id'];
        $models[$i]['model_title']                  = $row['model_title'];
        $models[$i]['model_year_of_publication']    = $row['model_year_of_publication'];
        $models[$i]['ca'] = array();
        $casql  = "SELECT ca.name AS cons_act
                FROM consulting_activities ca,
                    models m1
                    LEFT JOIN models_consulting_activities m2 ON m1.model_id = m2.model_id
                WHERE m1.model_id = '".$row['model_id']."'
                    AND m2.consulting_activity_id = ca.consulting_activity_id";
        $caresult = $db->query($casql);
        if ($caresult->numRows() > 0) {
            while ($caresult->fetchInto($carow)) {
                if (in_array($carow['cons_act'], $caArray)) {
                    $models[$i]['ca'][] = $carow['cons_act'];
                }
            }
            $models[$i]['ur'] = array();
            $ursql  = "SELECT ur.name AS user_role
                    FROM user_roles ur,
                        models m1
                        LEFT JOIN models_user_roles m2 ON m1.model_id = m2.model_id
                    WHERE m1.model_id = '".$row['model_id']."'
                        AND m2.user_role_id = ur.user_role_id";
            $urresult = $db->query($ursql);
            if ($urresult->numRows() > 0) {
                while ($urresult->fetchInto($urrow)) {
                    if (in_array($urrow['user_role'], $urArray)) {
                        $models[$i]['ur'][] = $urrow['user_role'];
                    }
                }
            }
        }
        ++$i;
    }
}
########EINDE TRAAG##########
$endtime = microtime();
$time .= "User roles + cons. activities: ".(($endtime-$starttime > 0) ? ($endtime-$starttime) : "0.00")."<br />";
$totaal += ($endtime-$starttime);
$starttime = microtime();

foreach ($models as $key => $model) {
    if (sizeof($model['ca']) == 0 || sizeof($model['ur']) == 0) {
        unset($models[$key]);
    } else {
        $models[$key]['ca'] = sizeof($model['ca']);
        $models[$key]['ur'] = sizeof($model['ur']);
    }
    if (isset($models[$key])) {
        $models[$key]['total'] = sizeof($model['ca'])+sizeof($model['ur']);
    }
}
$endtime = microtime();
$time .= "Lege records eruit: ".(($endtime-$starttime > 0) ? ($endtime-$starttime) : "0.00")."<br />";
$totaal += ($endtime-$starttime);
$starttime = microtime();

$tempmodels = array();
$i = 0;
foreach ($models as $key => $value) {
    $tempmodels[$i] = $value;
    ++$i;
}
$models = $tempmodels;
$endtime = microtime();
$time .= "Herindexeren: ".(($endtime-$starttime > 0) ? ($endtime-$starttime) : "0.00")."<br />";
$totaal += ($endtime-$starttime);
$starttime = microtime();

$modelslen = sizeof($models);
for ($i = 0; $i < $modelslen; ++$i) {
    for ($j = 0; $j < $modelslen; ++$j) {
        if ($i != $j) {
            if ($models[$j]['total'] < $models[$i]['total']) {
                $tempmodel = $models[$i];
                $models[$i] = $models[$j];
                $models[$j] = $tempmodel;
            } elseif ($models[$j]['total'] == $models[$i]['total']) {
                if ($models[$j]['ur'] > $models[$i]['ur']) {
                    $tempmodel = $models[$i];
                    $models[$i] = $models[$j];
                    $models[$j] = $tempmodel;
                }
            }
        }
    }
}
$endtime = microtime();
$time .= "Sorteren: ".(($endtime-$starttime > 0) ? ($endtime-$starttime) : "0.00")."<br />";
$totaal += ($endtime-$starttime);
$time .= "Totaal: ".(($totaal > 0) ? ($totaal) : "0.00");
echo $time;

[ Voor 11% gewijzigd door nemesis_ op 05-09-2005 17:29 ]

[gorgi_19]

Linkje doet het niet en staan je indexen goed?

[Cavorka]

Wat is er traag? Bench je hele script eens, door elke stap te timen. Dan weet je in ieder geval wat het probleem is.

Pas daarna is het zinvol om met suggestie te komen. Dus ligt het aan de queries, aan je looping, etc.

Indices staan wel goed in je DB?

*Edit: Gorgi:

[ Voor 4% gewijzigd door Cavorka op 05-09-2005 17:24 ]

[MisterData]

zet eens 'EXPLAIN' voor je query en kijk met phpMyAdmin vervolgens naar de uitkomst. Daar staat precies welke indices MySQL al dat niet gebruikt, daar valt meestal een hoop aan te 'tunen'

[nemesis_]

Volgens mij staan de indexes wel goed (wat is goed?)
De tabel models heeft een index op de primary key.
De koppeltabellen hebben op beide primaries een index.

Link is aangepast.

[nemesis_]

Dit krijg ik bij EXPLAIN bij de eerste query:

id  select_type  table   type  	 possible_keys                            key  	        key_len  ref  	                                                rows  	Extra
1   SIMPLE 	 ur 	 ALL 	 PRIMARY,user_role_id,name                NULL 	        NULL 	 NULL 	                                                6 	Using where; Using temporary; Using filesort
1   SIMPLE 	 m3 	 ref 	 PRIMARY,model_id,user_role_id            user_role_id 	4 	 provenmodels.ur.user_role_id 	                        135 	Using where
1   SIMPLE 	 ca 	 ALL 	 PRIMARY,consulting_activity_id,name      NULL          NULL 	 NULL 	                                                5 	Using where
1   SIMPLE 	 m1 	 eq_ref  PRIMARY,model_id                         PRIMARY 	4 	 provenmodels.m3.model_id 	                        1 	 
1   SIMPLE 	 m2 	 eq_ref  PRIMARY,model_id,consulting_activity_id  PRIMARY 	8 	 provenmodels.m1.model_id,provenmodels.ca.consultin... 	1 	Using where; Using index

[ Voor 20% gewijzigd door nemesis_ op 05-09-2005 20:14 ]

[Bosmonster]

Zoals ik het nu zie haal je per resultaat weer een hele berg data op (ik zie 2 geloopte SQL statements).

Met 500 resultaten betekent dat een hele hoop geloopte query's? (en laat dat nou precies tussen je ##TRAAG## blokken zijn..)

[ Voor 17% gewijzigd door Bosmonster op 05-09-2005 19:13 ]

[nemesis_]

@Bosmonster: ja, ik weet het. Maar heb je een beter idee? Ik heb al die gegevens nodig om de zoekresultaten op relevantie te rangschikken.
Ik heb het in één keer geprobeerd via MATCH(kolom) AGAINST(zoekwoorden), maar omdat er per model meerdere gegevens zijn (consulting_activities en user_roles) wilde dat niet.

[Bosmonster]

Dan lijkt het me een stuk handiger om naast je standaard tabellen, 1 tabel bij te houden voor de search, waarin je die velden koppelt aan je producten waar je op wilt zoeken.

Kijk trouwens ook even hier:

http://nl3.php.net/manual/en/function.microtime.php

En zie dat de manier waarop je nu met microtime werkt niet klopt.. ook wel handig als je wilt benchmarken

Daar zul je zoiets van moeten maken:

PHP:

function get_microtime() {
    $mt = explode(" ", microtime());
    return (float) $mt[1] + (float) $mt[0]; 
}

[ Voor 60% gewijzigd door Bosmonster op 05-09-2005 20:18 ]

[nemesis_]

1 tabel die dus heel groot wordt en waar alle data reduntant in opgeslagen worden?
MySQL is daar volgens mij dan niet het handigste voor, maar het zou kunnen.

[Bosmonster]

nemesis_ schreef op Monday 05 September 2005 @ 20:17:
1 tabel die dus heel groot wordt en waar alle data reduntant in opgeslagen worden?
MySQL is daar volgens mij dan niet het handigste voor, maar het zou kunnen.

Hoe denk je dat de search op GoT werkt bijvoorbeeld? En nog met MySQL ook!

Voor een zoekfunctie is het de normaalste zaak van de wereld om daar een losse index voor neer te zetten. Een stuk efficienter dan te gaan zoeken in tig tabellen en die tabellen te gaan vervuilen met loze indexen om een search over te houden die met query-loops aan elkaar gehangen is...

En van redundantie is dan uiteraard geen sprake.. je gebruikt de data in de zoekindex niet, die is alleen voor het linken van zoektermen aan een ID...

[ Voor 46% gewijzigd door Bosmonster op 05-09-2005 20:24 ]

[nemesis_]

Bosmonster schreef op maandag 05 september 2005 @ 20:13:
http://nl3.php.net/manual/en/function.microtime.php

En zie dat de manier waarop je nu met microtime werkt niet klopt.. ook wel handig als je wilt benchmarken

het duurt nu ruim 4 seconden, waarvan 2,8 seconden voor al die data ophalen per model

[nemesis_]

Bosmonster schreef op maandag 05 september 2005 @ 20:19:
En van redundantie is dan uiteraard geen sprake.. je gebruikt de data in de zoekindex niet, die is alleen voor het linken van zoektermen aan een ID...

Maar er staan dan toch wel data in als?:

model_id  iets_anders
1         "een"
1         "twee"
1         "drie"
1         "vier"

Dus per model_id alle opties die er zijn (combinaties met consulting_activities enzo)

[Bosmonster]

nemesis_ schreef op maandag 05 september 2005 @ 20:23:
[...]


het duurt nu ruim 4 seconden, waarvan 2,8 seconden voor al die data ophalen per model

Als je nou even naar dat linkje had gekeken

Voorbeeld code:

PHP:

<?php
function get_microtime() { 
    $mt = explode(" ", microtime()); 
    return (float) $mt[1] + (float) $mt[0];  
}

$start1 = microtime ();
$start2 = get_microtime ();
sleep (1);
$end1 = microtime ();
$end2 = get_microtime ();

echo '<pre>';

echo 'start1: ' . $start1 . "\n";
echo 'end1: ' . $end1 . "\n";
echo 'end1-start1: ' . ($end1-$start1) . "\n\n";

echo 'start2: ' . $start2 . "\n";
echo 'end2: ' . $end2 . "\n";
echo 'end2-start2: ' . ($end2-$start2);

echo '</pre>';
?>

Resultaat:

code:

start1: 0.36499800 1125945469
end1: 0.36874900 1125945470
end1-start1: 0.003751

start2: 1125945469.365
end2: 1125945470.3688
end2-start2: 1.0037639141083

Die eerste zit toch verdomd ver weg van de 1 seconde die het zou moeten zijn

M.a.w., microtimes kun je niet domweg van elkaar aftrekken, zoals je in de manual kunt lezen.

[ Voor 14% gewijzigd door Bosmonster op 05-09-2005 20:39 ]

[nemesis_]

Ik heb dat microtime_float() script gebruikt dat op php.net staat. Dan kan het nu toch 4(.nogwat eigenlijk) seconden duren

[ Voor 51% gewijzigd door nemesis_ op 05-09-2005 20:48 ]

[Gomez12]

Dus als ik het goed zie heb je in het meest negatieve geval 3 query's nodig voor 1 result??? Waarbij
query 1 meer dan 1 row kan bevatten waarop per row dan
query 2 meer dan 1 row kan bevatten waarop per row dan
query 3 uitgevoerd word.

Dus theoretisch kan het volgende gebeuren :

query 1 levert 1 result op. Query 2 ( wordt 1x uitgevoerd ) en levert 100 results op waardoor query 3 ( wordt 100x uitgevoerd ) 100 totaalresultaten oplevert.
Maar het volgende kan dus ook voorkomen
query 1 levert 100 resultaten op. Query 2 ( wordt 100x uitgevoerd ) en levert 100 results op per resultaat query 1 waardoor query 3 ( wordt 100.000x uitgevoerd ( 100x100 ) ) weer 100 results oplevert waardoor je een totaal van 100x100x100 results krijgt??? Waardoor er 1 if-instructie 1.000.000 x uitgevoerd wordt waarbij het netto resultaat kan zijn dat er maar 10 results uit komen rollen.

Oftewel kijk of je niet iets aan je data-model kan veranderen door 1 of meerdere koppeltabellen in te voeren zodat je het totaal met 1 query kunt bereiken.

[nemesis_]

@Gomez12: Ik kan gebruik maken van koppeltabellen, maar hoe kan ik steeds 1 record uit de database halen waar alle consulting_activities en alle user_roles etc aan hangen? Als ik niet ga groeperen oid dan heb ik ook duizenden rijen als uitkomst, wat ook niet de bedoeling is.

[ACM]

Bosmonster schreef op maandag 05 september 2005 @ 20:19:
Hoe denk je dat de search op GoT werkt bijvoorbeeld? En nog met MySQL ook!

Gebruikt de search van GoT mysql als zijn primaire doorzoekbare opslag? Dat is nieuw voor me.
Sinds ik dat ergens in 2002 verving door Xapian is het volgens mij toch echt niet meer verandert, terug naar mysql ofzo hoor

Voor een zoekfunctie is het de normaalste zaak van de wereld om daar een losse index voor neer te zetten. Een stuk efficienter dan te gaan zoeken in tig tabellen en die tabellen te gaan vervuilen met loze indexen om een search over te houden die met query-loops aan elkaar gehangen is...

Maar inderdaad, bij een zoekfunctie is indexering soms noodzakelijk. Zeker als er veel data te doorzoeken is.

[Bosmonster]

ACM schreef op maandag 05 september 2005 @ 21:59:
[...]

Gebruikt de search van GoT mysql als zijn primaire doorzoekbare opslag? Dat is nieuw voor me.
Sinds ik dat ergens in 2002 verving door Xapian is het volgens mij toch echt niet meer verandert, terug naar mysql ofzo hoor

Ow... nog maar ff meppen dan en bonken

[ACM]

nemesis_ schreef op maandag 05 september 2005 @ 17:20:
Een db-dump: db dump en dan pmtest.sql

Sja, je doet wellicht niet de handigste combinatie van queries. Maar onderzoek nog of je door toevoeging van indexen, anders dan de primary keys, nog wat kan winnen.

SQL:

SELECT ca.name AS cons_act
                FROM consulting_activities ca,
                    models m1
                    LEFT JOIN models_consulting_activities m2 ON m1.model_id = m2.model_id
                WHERE m1.model_id = '".$row['model_id']."'
                    AND m2.consulting_activity_id = ca.consulting_activity_id

Deze query heeft toch heel die models-tabel niet nodig?
Bovendien had je deze join ook al bij je 1e query staan, kan je daar niet wat handigers mee doen? Dat je het niet dubbelop gaat lopen ophalen dus, want die data had je ook al in je 1e query gehad als je niet groupeerde op de model_id...

SQL:

SELECT ur.name AS user_role
                    FROM user_roles ur,
                        models m1
                        LEFT JOIN models_user_roles m2 ON m1.model_id = m2.model_id
                    WHERE m1.model_id = '".$row['model_id']."'
                        AND m2.user_role_id = ur.user_role_id

Hier doe je hetzelfde. Je gaat neemt weer de models tabel in je query terwijl die helemaal niet nodig is voor deze query en bovendien is dit al data die in je 1e query geprocessed, maar door jou genegeerd is.

Volgens mij kan je beter je 1e query weghalen en dan je 1e+2e combineren en je 1e+3e. Of anders gewoon je 1e uitvoeren zonder de group by en je 2e en 3e niet.

[nemesis_]

SQL:

SELECT m1.model_id,
                title AS model_title,
                year_of_publication AS model_year_of_publication,
                GROUP_CONCAT(ca.name) AS ca,
                GROUP_CONCAT(ur.name) AS ur
            FROM models m1, user_roles ur, consulting_activities ca
                LEFT JOIN models_consulting_activities m2 ON m1.model_id = m2.model_id
                LEFT JOIN models_user_roles m3 ON m1.model_id = m3.model_id
            WHERE m3.user_role_id = ur.user_role_id
                AND m2.consulting_activity_id = ca.consulting_activity_id
                AND ur.name IN ('".$user_roles."')
                AND ca.name IN ('".$cons_acts."')
            GROUP BY m1.model_id
            ORDER BY m1.model_id

GROUP_CONCAT heeft het een heel stuk versneld. Nu alleen nog het sorteren eigenlijk.
Totaal is nu +/- 1 seconden, waarvan het sorteren 0,6 seconden en het zoeken 0,2 seconden duurt.
Dit stuk kan ook vast beter:

PHP:

$modelslen = sizeof($models);
for ($i = 0; $i < $modelslen; ++$i) {
    for ($j = 0; $j < $i + 1; ++$j) {
        if ($i != $j) {
            if ($models[$j]['total'] < $models[$i]['total']) {
                $tempmodel = $models[$i];
                $models[$i] = $models[$j];
                $models[$j] = $tempmodel;
            } elseif ($models[$j]['total'] == $models[$i]['total']) {
                if ($models[$j]['ur'] > $models[$i]['ur']) {
                    $tempmodel = $models[$i];
                    $models[$i] = $models[$j];
                    $models[$j] = $tempmodel;
                }
            }
        }
    }
}

[ Voor 12% gewijzigd door nemesis_ op 06-09-2005 19:53 ]

[nemesis_]

Het sorteren is nu ook enorm versneld, maar ik mis nu nog iets.

PHP:

$models = array_sort($models, 'total');

function array_sort($array, $key)
{
   for ($i = 0; $i < sizeof($array); $i++) {
       $sort_values[$i] = $array[$i][$key];
   }
   asort ($sort_values);
   reset ($sort_values);
   while (list ($arr_key, $arr_val) = each ($sort_values)) {
         $sorted_arr[] = $array[$arr_key];
   }
   return $sorted_arr;
}

Er wordt nu gesorteerd op $models[key]['total'] en dat werkt goed en snel, maar als de 'total''s gelijk zijn, zou hij ook nog moeten gaan sorteren op $models[key]['ur']. Alleen hoe gaan we dat doen?

[ Voor 6% gewijzigd door nemesis_ op 07-09-2005 13:50 ]

[nemesis_]

Ik heb nu een index-tabel gemaakt, dus het is allemaal niet meer nodig.
Deze kan ik sorteren op MATCH(kolom) AGAINST (zoekwoorden).