Query optimaliseren? gebruikt filesort en duurt vrij lang.

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Ericbruggema x

ericbruggema x

02/04/2013 08:10:53
Quote Anchor link
Iemand een idee hoe ik deze query beter kan schrijven?

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
SELECT `plaatjes`.*
FROM `plaatjes`
LEFT JOIN `views` ON `views`.`category` = `plaatjes`.`category`
WHERE YEAR(`views`.`date`) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(`views`.`date`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
GROUP BY `views`.`category`, `plaatjes`.`category`
ORDER BY `views`.`date` DESC
LIMIT 0,5


structuur en indexes
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE IF NOT EXISTS `plaatjes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category` varchar(32) CHARACTER SET latin1 NOT NULL,
  `filetype` tinyint(3) unsigned NOT NULL,
  `filename` varchar(40) CHARACTER SET latin1 NOT NULL,
  `filesize` int(10) unsigned NOT NULL,
  `height` int(10) unsigned NOT NULL,
  `width` int(10) unsigned NOT NULL,
  `animated` enum('j','n') CHARACTER SET latin1 NOT NULL DEFAULT 'n',
  `views` int(10) unsigned NOT NULL,
  `lastview` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `category` (`category`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC AUTO_INCREMENT=28350 ;

-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `views` (
  `category` varchar(32) NOT NULL,
  `date` date NOT NULL,
  `tstamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `views` int(10) unsigned NOT NULL,
  `thumbs` int(10) unsigned NOT NULL,
  `searches` int(10) unsigned NOT NULL,
  UNIQUE KEY `category` (`category`,`date`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


maar deze gebruikt filesort, iets wat ik graag wil voorkomen aangezien dat nogal in de snelheid beperkt.

Ene site laad met 5.000 view records in 4 seconden andere site met 35k records in views laad in 12-20 seconden :{

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
1,SIMPLE,views,index,category,category,37,NULL,35317,Using where; Using index; Using temporary; Using f...
1,SIMPLE,plaatjes,ref,category,category,34,nvt****_anipl.views.category,17


Iemand een idee?
Gewijzigd op 02/04/2013 09:06:56 door Ericbruggema x
 
PHP hulp

PHP hulp

03/12/2024 15:54:54
 
Erwin H

Erwin H

02/04/2013 08:59:26
Quote Anchor link
Je zegt dat de tweede site 35.000 records in de views tabel heeft staan en zo te zien in je query gebruik je geen limiet. Dat zal waarschijnlijk betekenen dat je probleem niet in de query zit, maar in het ophalen van de gegevens. Als je namelijk al die gegevens op het scherm wilt krijgen dan ben je wel even bezig. Dan kan je aan je query versnellen wat je wilt, maar dat gaat niets uitmaken.

Draai je query eens met een limit clausule op het einde (LIMIT 10 bijvoorbeeld), duurt het dan nog steeds te lang?
 
Ericbruggema x

ericbruggema x

02/04/2013 09:06:33
Quote Anchor link
Excuus, ik gebruik wel een limiet, LIMIT 0,5 :) dan duurt deze nog steeds erg lang.
 
Erwin H

Erwin H

02/04/2013 09:14:23
Quote Anchor link
ok, dan gaan we verder kijken :-)

Wat is 'category' in beide tabellen? Het staat er als een VARCHAR(32), maar is de waarde voor elke rij anders (free format), of is het werkelijk een category en heb je dus in feite maar een paar continu herhalende waardes? In dat laatste geval zou je dat beter kunnen normaliseren, want vergelijken en sorteren op strings is een stuk langzamer dan op integers.

Hoe moet ik je WHERE precies lezen, wil je alleen de views van de laatste maand hebben?

Waarom heb je die GROUP BY clause? Eigenlijk is die foutief, omdat je geen enkele aggregate functie gebruikt en zou je dat wel doen, dan moet je groeperen op alle niet aggregate kolommen in je select.
 
Ericbruggema x

ericbruggema x

02/04/2013 09:19:19
Quote Anchor link
Category bevat zo'n 2000 verschillende 'woorden', en met normaliseren doel je dan denk ik op het apart zetten van deze gegevens in een ander tabel en dan daaraan linken.

Met de where zoek ik in de views tabel naar alle 'gegevens' van de vorige maand.

De group by wil ik gebruiken om per category maar 1 plaatje te kunnen zien.
 
Erwin H

Erwin H

02/04/2013 09:38:04
Quote Anchor link
Ik zou het op deze manier proberen:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT plaatjes.*
FROM (
  SELECT MAX(plaatjes.id) AS id, plaatjes.category
  FROM (
    SELECT DISTINCT category
    FROM views
    WHERE date BETWEEN date_sub(CURDATE(), INTERVAL '1' month) and CURDATE()
    ORDER BY date DESC
  ) a
  LEFT JOIN plaatjes ON a.category = plaatjes.category
  GROUP BY plaatjes.category
) b
LEFT JOIN plaatjes ON b.id = plaatjes.id

Je selecteert dus eerst de categorieen die je nodig hebt op basis van de views. Daaraan koppel je een id van een plaatje (per category) en vervolgens join je plaatjes nogmaals om alle gegevens erbij te krijgen.
Enige wat ik denk dat nu niet zal gebeuren, is het sorteren op datum. Dat heb ik wel meegegeven in de subquery, maar wordt denk ik overruled door de GROUP BY later. Dit zou je even moeten testen, plus of het enigszins sneller is natuurlijk :-)
Gewijzigd op 02/04/2013 09:53:47 door Erwin H
 
Ericbruggema x

ericbruggema x

02/04/2013 09:46:31
Quote Anchor link
Ik krijg direct de volgende foutmelding;
Unknown column 'b.id' in 'on clause'

Ik gebruik deze query 2x, 1x voor een dag geleden en 1x voor een maand geleden. De query van een dag geleden draait af en toe vrij snel (binnen 0.1 seconde) maar daarna weer kei sloom. Het doel is om de hoofd categorieen die het beste bekeken zijn te tonen.
 
Erwin H

Erwin H

02/04/2013 09:54:29
Quote Anchor link
Duidelijk, een alias vergeten in de tweede subquery:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
SELECT MAX(plaatjes.id) AS id

(staat nu boven er ook bij)
 
Ericbruggema x

ericbruggema x

02/04/2013 10:13:51
Quote Anchor link
Deze is duidelijk een stuk sneller! maar helaas doet het niet wat ik wil dat de query doet, het laten zien van de laatst geziene categorieen (+ 1 link naar plaatje) van de gisteren/vorige maand. Het toont een overzicht van afbeeldingen per categorie, niet gesorteerd op laatst gezien.

Misschien een idee om eerst de views uit te lezen en daarbij het best bekeken plaatje te zoeken oid? alleen zit ik nu al een tijdje te dokteren hoe ik dit voor elkaar krijg...
 
Erwin H

Erwin H

02/04/2013 10:17:59
Quote Anchor link
Volgens mij kan dat met een simpele aanpassing:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
    LIMIT 5
  ) 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

Hier zit de limit dus ook bij, zodat je alleen de laatste 5 (of aan te passen) categorieen krijgt.
Gewijzigd op 02/04/2013 10:19:43 door Erwin H
 
Ericbruggema x

ericbruggema x

02/04/2013 10:27:18
Quote Anchor link
Dat is duidelijk, alleen krijg ik 3 goede resutaten en 2 slechte waarbij ik alleen maar NULL zie?

Wijzigen, Kopiëren, Verwijderen,90,3dsmileys,1,3dsmileys9.gif,51177,71,71,j,1,2013-03-07 11:48:18
Wijzigen, Kopiëren, Verwijderen,30,1april,2,1april9.jpg,16121,321,401,n,1,2013-03-10 08:27:53
Wijzigen, Kopiëren, Verwijderen,60,3d,2,3d9.jpg,6276,100,100,n,3,2013-03-26 21:30:30
Wijzigen, Kopiëren, Verwijderen,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
Wijzigen, Kopiëren, Verwijderen,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

Want alles in de database is gekoppeld, er zijn geen 'lege' records of records die niet verbonden zijn met de view (view werkt op basis van het plaatjes tabel)
 
Erwin H

Erwin H

02/04/2013 10:33:46
Quote Anchor link
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:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.
 
Ericbruggema x

ericbruggema x

02/04/2013 10:56:30
Quote Anchor link
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...
 
Erwin H

Erwin H

02/04/2013 11:10:11
Quote Anchor link
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.
 
Ericbruggema x

ericbruggema x

02/04/2013 14:24:52
Quote Anchor link
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! :)
 
Ericbruggema x

ericbruggema x

03/04/2013 21:16:07
Quote Anchor link
Ik heb een testje gedaan, en wat blijkt 2 queries zijn sneller ;)

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
<?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 />";
[
/code]

uitkomst:
[
code]
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
[/code]

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

Online versie vertelt nog een mooier verhaal.

[
code]
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
[/code]

In mijn lokale versie zijn er nog geen statistieken van 'vandaag' / 'gisteren' online wel..
Gewijzigd op 03/04/2013 21:31:38 door ericbruggema x
 
Erwin H

Erwin H

03/04/2013 22:22:24
Quote Anchor link
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.
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

03/04/2013 23:18:41
Quote Anchor link
Dit is ook een beetje appels met peren vergelijken. de twee queries in 1 query:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
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:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
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)
Gewijzigd op 03/04/2013 23:29:18 door Ger van Steenderen
 
Ericbruggema x

ericbruggema x

06/04/2013 22:54:35
Quote Anchor link
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!
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.