slow dashboard routine, performance improvement suggestions

You have a suggestion for a future version of phpMyFAQ? Then post it here!

Moderator: Thorsten

Post Reply
Lidio
Posts: 25
Joined: Mon Dec 10, 2007 9:26 pm

slow dashboard routine, performance improvement suggestions

Post by Lidio »

Hi Thorsten,

In version 2.8.19 (with the index on time for session table) I still find that the loading to the dashboard slow when there are "plenty" of session records. This appears to be the result from the function getLast30DaysVisits()

Code: Select all

    /**
     * Calculates the number of visits per day the last 30 days
     *
     * @returns array
     */
    public function getLast30DaysVisits()
    {
        $stats  = array();
        $visits = array();

        $startDate = strtotime('-1 month');
        $endDate   = $_SERVER['REQUEST_TIME'];

        $query = sprintf("
            SELECT
                sid, time
            FROM
                %sfaqsessions
            WHERE
                time > %d
            AND
                time < %d;",
            PMF_Db::getTablePrefix(),
            $startDate,
            $endDate
        );
        $result = $this->_config->getDb()->query($query);

        while ($row = $this->_config->getDb()->fetchObject($result)) {
            $visits[] = $row->time;
        }

        for ($date = $startDate; $date <= $endDate; $date += 86400) {
            $stats[date('Y-m-d', $date)] = 0;
            foreach ($visits as $visitDate) {
                if (date('Y-m-d', $date) === date('Y-m-d', $visitDate)) {
                    $stats[date('Y-m-d', $date)]++;
                }
            }
        }

        return $stats;
    }
Two suggestions:
(1) I did not see any usage of the "sid" from the SELECT statement thinking it could be removed thus only selecting time which is already indexed.

(2) replace the tallying loop with the following for the same results:

Code: Select all

        for ($date = $startDate; $date <= $endDate; $date += 86400) {
            $stats[date('Y-m-d', $date)] = 0;
        }
        foreach ($visits as $visitDate) {
            $stats[date('Y-m-d', $visitDate)]++;
        }
This eliminates unnecessary iterations and multiple calls to the "date" function.

Both changes have a significant performance improvement on my site.

As always, thanks for all the work.

Lidio.
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: slow dashboard routine, performance improvement suggesti

Post by Thorsten »

Hi,

thanks a lot, you're right. Fixed for 2.8.20: https://github.com/thorsten/phpMyFAQ/co ... 0a46032e98

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
Post Reply