Query optimaliseren? gebruikt filesort en duurt vrij lang.

Ah, zal aan de LIMIT liggen denk ik. Ik denk dat die LIMIT wordt geforceerd voor de GROUP BY wordt uitgevoerd. Probeer eens de LIMIT uit de subquery te halen en aan het einde te zetten:

SELECT plaatjes.*
FROM (
  SELECT MAX(plaatjes.id) AS id, plaatjes.category, a.date_viewed
  FROM (
    SELECT category, MAX(date) AS date_viewed
    FROM views
    WHERE date BETWEEN date_sub(CURDATE(), INTERVAL '1' month) and CURDATE()
    GROUP BY category
  ) a
  LEFT JOIN plaatjes ON a.category = plaatjes.category
  GROUP BY plaatjes.category, a.date_viewed
) b
LEFT JOIN plaatjes ON b.id = plaatjes.id
ORDER BY b.date_viewed DESC
LIMIT 5

Dit zou wel kunnen betekenen dat de query trager wordt. Als het weer te traag wordt kan het nog wel iets omgebouwd worden.

Ja qua tijd scheelt het zeker een stuk maar zit per query nog ruim boven de 1seconde ;{

Zit er nu te denken of het niet handiger is om deze query gewoon op te splitsen in 2, dan lees ik eerst de views uit en pak daarna de plaatjes er bij...

Nee, 2 queries zijn altijd langzamer dan 1.

Maar na een test hier, zou het eerdere probleem van die NULL waardes niet in die LIMIT moeten zitten. Die zou gewoon de juiste waardes moeten opleveren en als er minder dan 5 rijen zijn er ook minder terug moeten geven.

Een andere mogelijkheid is nog om beide LEFT JOINs om te schrijven naar een INNER JOIN. Als dat het probleem ook niet verhelpt dan moet je dieper kijken naar waar precies het probleem zit. Ergens in een subquery krijg je dan gegevens terug die niet de bedoeling zijn, maar puur op de code (zonder enige inzicht in je data) kan ik dat verder niet doen.

Erwin, dat 2 queries langzamer zijn dan 1 hoeft niet altijd zo te zijn, heb zelf een statistieken script in elkaar gedraaid en moest daarvoor 1 query verdelen in 2 om de preformance goed te houden maar goed, dat was een heel ander project, vele jaren geleden.

Maar goed, de query is nog niet zo snel als dat ik verwacht had. Er moet toch een andere oplossing zijn, als je wilt kan ik je via PM wel inlog gegevens geven zodat je kunt spelen met de database of als je wilt heb ik hier wel een sql bestand met alle records.

Heb het nu tijdelijk maar even gecached, scheelt een berg maar is niet de oplossing die ik voor ogen had! :)

Ik heb een testje gedaan, en wat blijkt 2 queries zijn sneller ;)

<?php

function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

function getRecordsByDate(&$db, $search = '1 DAY')
{
    $sql = "SELECT SQL_NO_CACHE category, MAX(date) AS date_viewed
            FROM views
            WHERE date BETWEEN date_sub(CURDATE(), INTERVAL " . $search . ") and CURDATE()
            AND `category` != ''
            GROUP BY category
            LIMIT 5";
    $db->q($sql);
    if ($db->rows() > 0)
    {
        $l = array();
        foreach ($db->fetch() AS $k=>$v)
        {
            $l[$v['category']] = $v['date_viewed'];
        }
        
        if (count($l) > 0)
        {
            $sql = "SELECT `plaatjes`.*
                    FROM `plaatjes`
                    WHERE `category` IN ('" . implode("','", array_keys($l)) . "')
                    GROUP BY `plaatjes`.`category`
                    ORDER BY `plaatjes`.`views` DESC";
            $db->q($sql);
            
            return $db->fetch();
        }
    }
    return array();
}


function getRecordsByDateOld(&$db, $search = '1 DAY')
{
    $sql = 'SELECT SQL_NO_CACHE plaatjes.*
            FROM (
                  SELECT MAX(plaatjes.id) AS id, plaatjes.category, a.date_viewed
                  FROM (
                        SELECT category, MAX(date) AS date_viewed
                        FROM views
                        WHERE date BETWEEN date_sub(CURDATE(), INTERVAL '  . $search . ') and CURDATE()
                        AND `category` != \'\'
                        GROUP BY category
                       ) a
                  LEFT JOIN plaatjes ON a.category = plaatjes.category
                  GROUP BY plaatjes.category, a.date_viewed
                ) b
            LEFT JOIN plaatjes ON b.id = plaatjes.id
            ORDER BY b.date_viewed DESC
            LIMIT 5';
    
    $db->q($sql);
    if ($db->rows() > 0)
    {
        return $db->fetch();
    }
    else
    {
        return array();
    }
}

echo '1 day old function<br />';
$time_start = microtime_float();
for ($x = 0; $x < 100; $x++)
{
    getRecordsByDateOld($db, '1 DAY');
}
echo round(microtime_float() - $time_start, 2) . " secs<br />";

echo '1 day new function<br />';
$time_start = microtime_float();
for ($x = 0; $x < 100; $x++)
{
    getRecordsByDate($db, '1 DAY');
}
echo round(microtime_float() - $time_start, 2) . " secs<br />";

echo '1 month old function<br />';
$time_start = microtime_float();
for ($x = 0; $x < 100; $x++)
{
    getRecordsByDateOld($db, '1 MONTH');
}
echo round(microtime_float() - $time_start, 2) . " secs<br />";
echo '1 month new function<br />';
$time_start = microtime_float();
for ($x = 0; $x < 100; $x++)
{
    getRecordsByDate($db, '1 MONTH');
}
echo round(microtime_float() - $time_start, 2) . " secs<br />";

uitkomst:

1 day old function
4.63 secs
1 day new function
4.51 secs
1 month old function
33.67 secs
1 month new function
0.19 secs

met name de maand functie is extreem snel.. waarom? geen idee... nu eens ff online testen.

Online versie vertelt nog een mooier verhaal.

1 day old function
55.34 secs
1 day new function
0.19 secs
1 month old function
79.06 secs
1 month new function
0.19 secs

In mijn lokale versie zijn er nog geen statistieken van 'vandaag' / 'gisteren' online wel..

Interessant, wat bij mij voornamelijk de vraag oproept of die enkele query dan toch nog veel beter te optimaliseren valt. Op zich kan het niet zo zijn dat je met de overhead van een extra database aanroep toch sneller kunt zijn. Overigens ga ik er wel vanuit dat er ook daadwerkelijk uitkomsten uit komen in beide gevallen, anders is het verschil natuurlijk snel te verklaren....

Nu overigens geen puf om nog eens naar die query te kijken, misschien morgen.

Dit is ook een beetje appels met peren vergelijken. de twee queries in 1 query:

SELECT v.category, v.date, p.filename
FROM
	(SELECT category, date
	FROM views
	WHERE date BETWEEN CURRENT_DATE - INTERVAL 1 MONTH AND CURRENT_DATE
	ORDER BY date DESC LIMIT 5) v
JOIN
	plaatjes p ON v.category = p.category
GROUP BY v.category

Wat dus een oneigenlijk gebruik van group by is (in Postgres kan dit niet eens).

Het best bekeken plaatje van een categorie erbij halen:

SELECT v.category, v.date, p.filename, p.views, MAX(p.views) max_views
FROM
	(SELECT category, date
	FROM views
	WHERE date BETWEEN CURRENT_DATE - INTERVAL 1 MONTH AND CURRENT_DATE
	ORDER BY date DESC LIMIT 5) v
JOIN
	plaatjes p ON v.category = p.category
GROUP BY v.category, v.date, p.filename, p.views
HAVING p.views = MAX(p.views)

Allemaal bedankt voor jullie reacties, wat ik ook heb geprobeerd uit eindelijk is de versie met 2 queries vele malen sneller.... waarom? joost mag het weten, gebruik dan wel optimaal de 'simpele' indexen (simpele site zou je zeggen..).

Heb zeker wat geleerd dus het was zeker niet voor niets!
Bedankt!