[MySQL] Statistieken cachen

[MueR]

In ons bedrijf wordt regelmatig een statistieken pagina bekeken, met daarop dingen als omzet per product, betaalmethoden en meer. Deze pagina wordt uit MySQL gehaald. De originele programmering was haastwerk, en is dus erg slecht opgezet. Na 2 jaar is de tabel met transacties (waaruit de statistieken worden berekend) ruim 1GB groot, en bevat een paar miljoen records. De gemiddelde pageload tijd ligt rond de minuut, waarbij het CPU gebruik ontzettend hoog is.
Dit wil ik gaan optimaliseren, door de statistieken per dag te gaan opslaan in een cache tabel, en alleen uit de transactie tabel te selecten wanneer een specifieke datum wordt opgevraagd. Het script wat ik heb is bijna af, ik loop alleen tegen een probleem aan.

In de tabel transactions staat een veld 'tra_value', wat het bedrag aangeeft, en een veld 'tra_direction'. Dit laatste veld geeft aan of het geld binnenkomt (1) of naar buiten gaat (0). Het zou natuurlijk makkelijker zijn om minwaarden te gebruiken in de value tabel, maar aangezien dit al enkele jaren op verschillende websites wordt gebruikt, is het te tijdrovend om het goed aan te passen.

Met de query die ik nu heb, wordt per dag de totale omzet gehaald. Ik krijg dan per dag 2 rijen, 1 met direction 1, en een met direction 0. Dit wil ik eigenlijk combineren, zodat ik een total_incoming en total_outgoing krijg, maar ik kom er niet uit hoe. Kan iemand me een beetje op weg helpen?

Mijn huidige SQL query:

PHP:

$sql = "SELECT  DATE_FORMAT( `tra_datetime` , '%Y' ) AS `year`,
                DATE_FORMAT( `tra_datetime` , '%m' ) AS `month`,
                DATE_FORMAT( `tra_datetime` , '%d' ) AS `day`,
                `tra_platform_id`,
                `tra_affiliate_id`,
                `tra_direction`,
                SUM(`tra_value`) AS totaal
        FROM    `tbl_transaction`
        WHERE   DATE_FORMAT( `tra_datetime` , '%Y' ) = '".$cYear."'
            AND DATE_FORMAT( `tra_datetime` , '%m' ) = '".$cMonth."'
            AND `tra_status` = 1
        GROUP   BY  `day`,
                    `tra_direction`
        ORDER   BY  `day`   ASC,
                    `tra_direction`";

[Verwijderd]

Ik denk dat je voor dit probleem het beste via ETL-processen de statistische gegevens in datawarehouse(s) kan plaatsen.

http://forums.mysql.com/read.php?32,49873,49873

http://www.pesg.com/datawarehouseprinc.html

Dit is een onderdeel van business intelligence ik zou hier me hier ook verder in verdiepen.

[ Voor 17% gewijzigd door Verwijderd op 12-02-2007 12:34 ]