Optimering och index

Hej igen.

Gjorde en tråd för några dagar sen där jag skrev att mysql tar upp all processorkraft. Har nu hittat två frågor som jag tror är en väldigt stor bidragande orsak till detta. Skulle vara jätteschyst om ni som kan lite mer kunde komma med förslag på hur dessa ska byggas om och hur det ska indexeras.

Först är det denna, som hämtar ut information om en grupp. Fyra lag i varje grupp och i vissa turneringar är det 128 lag. Denna fråga kan alltså köras 32 gånger per sidomladdning på en av de sidor som visas allra oftast. Borde kanske kunna sätta första order by på gruppens id. Hm. Säg att den körs en gång per sida, då. Går den fortfarande att förbättra?

SELECT DISTINCT cuplag.id, cuplag.clanid, cuplag.groupwon, cuplag.groupdraw, cuplag.groupplusminus, cuplag.grouppoints, cuplag.id as cupida 
FROM cuplag, cupgroup 
WHERE cupgroup.cupid = "& rs("cupid") &" AND cupgroup.whatgroup = "& m &" AND (cupgroup.cuplag1 = cuplag.id OR cupgroup.cuplag2 = cuplag.id OR cupgroup.cuplag3 = cuplag.id  OR cupgroup.cuplag4 = cuplag.id)
ORDER BY cuplag.grouppoints desc, cuplag.groupplusminus desc, cuplag.seed asc

Sen har vi denna som hämtar ut senaste aktivitet i forumet. Körs på varje sida.

select f1.thread_id, f1.thread_name, f1.thread_author_id, f1.thread_date, f1.thread_text, f2.thread_reply_id, f1.thread_locked, f1.thread_sticky, f1.thread_stickytext,
	max(coalesce(f2.thread_date, f1.thread_date)) AS lastPostDate
	from forum_threads f1 left outer join forum_threads f2
	on f1.thread_id = f2.thread_reply_id
	where f1.thread_reply_id = 0 and f1.thread_deleted = 0 and not f1.thread_adminforum = 1
	group by f1.thread_id, f1.thread_name, f1.thread_author_id, f1.thread_text, f2.thread_reply_id, f1.thread_locked, f1.thread_sticky, f1.thread_stickytext
	order by lastPostDate desc
	limit 5

Finns det någon grundregel vid indexering? Indexera de fält som finns i WHERE?

Unika värden (ex. id, namn, datum) är bra att indexera.

Det känns som att cupgroup.cuplag1, cupgroup.cuplag2 osv är byggt för att minska antalet poster i tabellen? Men det gör det samtidigt svårare att bygga snabba frågor.

Normalt när man indexerar så pratar man om att kolumnen måste innehålla data som är max 1% unikt. Först då kan man dra nytta av index.

Jag vet inte alls hur din data ser ut så det är svårt att ge något annat än generella tips.

Du skall troligen inte indexera alla fält i ditt wherevillkor, men det är också svårt att svara på. Jag vet inte vad whatgroup innehåller. Ibland kan det löna sig att indexera alla kolumner i selectsatsen och wherevillkoret och då få ett covering index, men ibland hjälper det inte. Just optimering är svårt. Allt beror på dina krav och hur din DDL och data ser ut.

En sak som kan hjälpa rent prestandamässigt är att cacha beräknad data. Så istället för att göra denna beräkningen varje gång du får ett anrop, så har du en statisk tabell som uppdateras, säg var 5:e minut eller så ofta som du vill. När du sedan vill skriva ut alla dina 32-lag så är det en enkel selectsats, utan joins och besvärliga uträkningar. Detta löser dock inte dina problem med en långsam fråga, men det löser troligen CPU-problematiken eftersom frågan inte kommer att köras lika ofta.

Dock tror jag att denna frågan borde gå att optimera. Har du möjlighet att påverka tabellstrukturen? Ge oss gärna lite exempeldata också, samt beskriv tabellstrukturen.

Angående den sista frågan så kan du prova om det hjälper att lägga ett index på lastPostDate (desc).

cupgroup består av id, cupid, cuplagN och whatgroup. cuplag1 till cuplag4.

cuplag består av id, cupid, clanid, groupwon, groupdraw, groupplusminus och grouppoints. clanid är id i tabellen cupclans.

cupmatcher består av id, cupid, lag1, lag2, rundor1, rundor2 och groupnr. lag1 och lag2 är id:n i cuplag.

cupclans består av ett namn och ett id och används i stort sett bara för att hämta ut namnet på laget.

Kanske kan vara lite svårt att förstå. Får försöka göra ett bättre försök isåfall.

Hur ska jag kunna lägga ett index på den? Den finns ju inte i databasen utan är ett värde som byggs i sql-satsen. Menar du att jag ska indexera thread_date? Den är redan indexerad, isåfall. :)

Tycker ni att det känns rimligt att en sida med kanske 3000 besökare om dagen plockar ner en hel mysql-server? Låter det inte snarare som en hög eviga loopar än dålig optimering? :z