Query optimaliseren? gebruikt filesort en duurt vrij lang.
Door
ericbruggema x
op 02-04-2013 08:10
gewijzigd op 02-04-2013 09:06
2.495 views
Iemand een idee hoe ik deze query beter kan schrijven?
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
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 :{
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
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?
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.
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.
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 :-)
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.
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...
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.
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)
