Query optimaliseren? gebruikt filesort en duurt vrij lang.
Code (php)
1
2
3
4
5
6
7
8
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
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)
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
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;
`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)
1
2
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
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
Draai je query eens met een limit clausule op het einde (LIMIT 10 bijvoorbeeld), duurt het dan nog steeds te lang?
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.
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.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
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
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
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.
Code (php)
1
SELECT MAX(plaatjes.id) AS id
(staat nu boven er ook bij)
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...
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
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
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)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
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.
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...
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.
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! :)
Code (php)
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
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..
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
Nu overigens geen puf om nog eens naar die query te kijken, misschien morgen.
Code (php)
1
2
3
4
5
6
7
8
9
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
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)
1
2
3
4
5
6
7
8
9
10
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)
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
Heb zeker wat geleerd dus het was zeker niet voor niets!
Bedankt!