[PHP/MYSQL] Resultaten sneller ophalen

[console]

Goeiedag,

Ik ben met een script bezig die een aantal resulaten op aan de hand van een paar colums. Het duurd nu voor 4/5 colums rond de 4 seconden om data op te halen en te matchen.

1. Ik haal eerst alle kolommen op van de tabel die aangeroept word.
2. Dan kijk ik of er in de kolomnaam 'pipeline' in zit en geen 'website'.
3. Omdat er meerdere zelfde resulaten zijn wil ik dmv een INSTINCT alle resulaten die het zelfde zijn één maal terug krijgen waar ook nog eens een WHERE aan vast zit.
4. Daarna word dmv een where een match gemaakt; een afkorting zoals 'NL' word dan 'Nederland', die 'NL' komt dan uit de 3de stap.
5. Daarna voer ik een sum om de totalen te krijgen.
6. Alles word nu in een array gestopt
7. Array word gesorteerd op de 'commission'
8. En er word nog een array_reverse opgeroepen om de 'commission' boven aan te krijgen.

Gemiddeld duurt het 1 seconden voor dat hij deze bovenstaande stappen heeft uitgevoerd. De hoofd tabel waar alle data instaat wat gelogt word is nu nog maar 11.000 rijen groot. De listdata tabel is nu ongeveer 306 rijen groot.

Nu ben ik bang als de data aardig oploopt dit traag gaat worden. De hoofd tabel kan straks wel (minimaal) 5.000 rijen per dag gaan loggen en de listdata tabel kan dan bv 10.000 rijen bevatten.

Stukje code wat ik gebruik;

PHP:

    // Haal dynamische columns op
    $sql_columns = mysql_query( "SHOW COLUMNS FROM ". $_GET['TABLE'] );
    if( mysql_num_rows( $sql_columns ) ) {
        $i=0;
        while( $cr = mysql_fetch_object( $sql_columns ) ){
            if( stristr( $cr->Field, 'pipeline' ) && !stristr( $cr->Field, 'website' ) ) {
                ${$cr->Field} = array();
                echo '<div class="selector">';
                echo '<span><a name="sortbox" style="cursor:pointer;" onclick="javascript:sort(\''.$cr->Field.'\', \''.$JWHERE[1].'\', \'clicks\' );">Cli.</a> | <a name="sortbox" style="cursor:pointer;" onclick="javascript:sort(\''.$cr->Field.'\', \''.$JWHERE[1].'\', \'commission\' );">Com.</a> | <a name="sortbox" style="cursor:pointer;" onclick="javascript:sort(\''.$cr->Field.'\', \''.$JWHERE[1].'\', \'epc\' );">Epc.</a> | <a name="sortbox" style="cursor:pointer;" onclick="javascript:sort(\''.$cr->Field.'\', \''.$JWHERE[1].'\', \'sales\' );">Sal.</a></span><br/>';
                echo '<select name="count_'. $cr->Field .'" size="1"  multiple="multiple" class="listauto" id="count_'. $cr->Field .'">';
                $sql_types = mysql_query( "SELECT DISTINCT ". $cr->Field ." FROM ". $_GET['TABLE'] ." WHERE ". $cr->Field ." != '' AND analytic_timestamp ". $BETWEEN ." ". $WHERE );
                if( mysql_num_rows( $sql_types ) ) {
                    while( $tr = mysql_fetch_object( $sql_types ) ) {
                        $sql_data = mysql_query( "SELECT list_replace FROM listdata WHERE list_website = '". $_GET['TABLE'] ."' AND list_table = '". $cr->Field ."' AND list_find = '". $tr->{$cr->Field} ."' " );
                        $dr = mysql_fetch_object( $sql_data );
                        if( mysql_num_rows( $sql_data ) ) {
                            $sql_math = mysql_query( "SELECT SUM(analytic_commission) as commission FROM ". $_GET['TABLE'] ." WHERE analytic_timestamp ". $BETWEEN ." AND ". $cr->Field ." = '". $tr->{$cr->Field} ."' ". $WHERE );
                            $mr = mysql_fetch_object( $sql_math );
                            if( $mr->commission > 0 ) {
                                ${$cr->Field}[$i]['name'] = $dr->list_replace;
                                ${$cr->Field}[$i]['commission'] = $mr->commission;
                            }
                        }
                        $i++;
                    }
                }   
                ${$cr->Field} = orderBy( ${$cr->Field}, 'commission' );
                ${$cr->Field} = array_reverse( ${$cr->Field} );
                foreach( ${$cr->Field} as $key => $value ) {
                    echo '<option value="">'. $value['name'] .' => &euro; '. number_format( $value['commission'], 2 ) .'</option>';
                    echo "\n";
                }   
                echo '</select>';
                echo '</div>';
            }               
        }   
    }

[X_lawl_X]

Misschien moet je eens proberen om een cache-systeem op te zetten?

[MueR]

Ik hoef je vast niet uit te leggen dat $_GET in deze gebruiken behoorlijk onveilig is? Overigens vraag ik me af waarom je uberhaupt columns moet ophalen, behalve als je per $item een kolom in je tabel aanmaakt, wat aan mij meteen de reactie "stop met optimaliseren, begin met fatsoenlijk bouwen, opnieuw die hap" ontlokt.

[console]

MueR schreef op woensdag 19 mei 2010 @ 16:32:
Ik hoef je vast niet uit te leggen dat $_GET in deze gebruiken behoorlijk onveilig is? Overigens vraag ik me af waarom je uberhaupt columns moet ophalen, behalve als je per $item een kolom in je tabel aanmaakt, wat aan mij meteen de reactie "stop met optimaliseren, begin met fatsoenlijk bouwen, opnieuw die hap" ontlokt.

Van die GET weet ik, is alleen om te testen om verschillende tabellen snel aan te roepen - intern.

Ik moet data bijhouden op van verschillende website's ( elke website heeft zijn eigen tabel ) en het moet overal universeel implenteerbaar zijn. Dus zijn er een aantal kolommen dynamisch, die worden opgebouwd aan de hand van een reeks tekens/cijfers die mee worden gestuurd met een uitgaande klik. bv; jantje|01|HM|784567654 of bij een andere site; pukje|7326|test|jan|klaas|87239423476 dus heeft meer kolommen dan de eerste. Die | worden geexplode en elk stukje word dan in een vooraf aangemaakte kolomnamen geinsert.

Misschien is dit niet de allerbeste oplossing? Maar als jullie iets beter weten hoor ik dit graag

[FragFrog]

MueR schreef op woensdag 19 mei 2010 @ 16:32:
Overigens vraag ik me af waarom je uberhaupt columns moet ophalen, behalve als je per $item een kolom in je tabel aanmaakt,

Als ik zijn code goed begrijp haalt hij eerst een lijst met alle columns van een tabel op en gaat'ie daarna, per column (mits deze tenminste het woord 'pipeline' bevat en niet 'website') de resultaten voor die column opzoeken en weergeven, waarbij per uniek record in die column de list_replace (ergo, naam?) en sum van analytic_commission berekend wordt.

Having said that, je voert nu een SQL query uit per row in je database, per column in die ene table. Dat gaat, heel snel, heeeeel erg traag worden. Kun je dat herschrijven naar een enkele SQL query? Ja, zeer waarschijnlijk wel, maar je code is zo'n ongelovelijk ranzige bende dat ik me er niet aan durf te wagen je hiermee op weg te helpen. Je zit 7 niveau's diep te nesten, gebruikt variabele variabelen, schrijft je SQL queries volledig op een enkele regel (in PHP mag je gewoon enters in strings zetten, het is echt geen ramp je queries netjes uit te schrijven), mixt PHP en HTML door elkaar en gebruikt ook nog eens een dynamisch data model (wat volgens mij trouwens niet nodig is als je simpelweg specificeert welke kolommen je nodig hebt). Mijn excuses als ik hard overkom, maar je zal jezelf een plezier doen door dit eerst netjes te gaan herschrijven en goed na te denken over hoe je alle data nodig hebt.
Al met al:

"stop met optimaliseren, begin met fatsoenlijk bouwen, opnieuw die hap"

Begin er eens mee met eerst alle gegevens op te halen (in een array of object zo je wilt), die zo te ordenen als je uiteindelijke datamodel (dus bijvoorbeeld een tweedimensionale array voor een overzichtstabel) en daarna pas die data weer te geven. Dan hoef je niet per regel opnieuw een query te doen maar kun je in een keer alles ophalen en daarna door een array loopen - kost je een klein beetje geheugen maar bespaart je heel veel queries

//edit
Aangaande je inkomende data: een pipe-seperated-string is niet de meest robuste manier om gegevens over te brengen. Het hangt er een beetje vanaf waar deze vandaan komt: wordt die lokaal gegenereerd (dan kun je wellicht beter met ID's werken en data uit je database of bijvoorbeeld een sessie / memcache server halen), komt die van een externe server (dan is XML-RPC / SOAP veiliger) of komt dat van een user op een externe server als bijvoorbeeld een GET variabele (in welk geval het op zich wel prima is mits je maar goed escaped).

[ Voor 29% gewijzigd door FragFrog op 19-05-2010 18:12 ]