Index of niet op een kolom

[RSD]

Met veel zaken, red ik me meestal wel. Het zal meestal niet optimaal zijn, maar goed. Dat maakt even niet uit. Nu ben ik op het punt gekomen, dat ik het allemaal niet meer red en volgens mij is het database model ook erg beroerd.

Ik heb een soort van weblog welke best druk bezocht wordt. Echter heb ik Jet Profiler gebruikt om te kijken welke queries er nu verkeerd gaan en aan Jet Profiler te zien is dat zo'n beetje alles. Nu wil ik het database model helemaal opnieuw gaan opzetten.

Het gaat hem er vooral om dat ik 12000 posts heb in een tabel. Af en toe staat de status van een post of offline (status=0) en die zal dan niet moeten worden getoond. Ik zelf doe dan iets als SELECT id,title FROM post WHERE status=1. Om het aantal posts op te halen voor pagination doe ik dan iets als SELECT COUNT(*) FROM posts WHERE status=1.

Nu zegt Jet Profiler dat deze query heel erg langzaam is. Dit klopt ook, maar ik dacht omdat ik een index had op de status dat dit wel redelijk snel moest zijn, maar dat is dus niet zo.

Nu zit ik te denken om in de tabel post alleen maar die rijen te plaatsen die ook online zijn.

Ik kan het natuurlijk ook normaliseren door het status veld in een aparte tabel te zetten met daarin het postid en het statusid.

Dus:

post
- id
- title

status
- id
- code

poststatus
- id
- postid
- statusid

Nu ben ik wel benieuwd wat eigenlijk het beste is. Zoveel mogelijk normaliseren en als dat het geval is, moet ik dan ook een index plaatsen op het veld id in de tabel status (dit is een kleine tabel, dus zelf denk ik van niet (maar het id is al een PK dus het gebeurt eigenlijk al, hoe kan ik dat dan voorkomen als er geen index op moet?)) en een index over postid,statusid (of is het beter om een index te plaatsen over statusid,postid)? Of moet daar ook geen index op staan.

Het gaat er dus om dat ik sommige posts uit moet kunnen zetten. Of is het beter om die in een andere tabel te plaatsen.

Dit probleem kom ik eigenlijk continue tegen, ook zo met het indelen in rubrieken etc... Het is misschien heel basic, maar ik worstel er al heel lang mee. Uit boeken weet ik dat er zoveel mogelijk genormaliseerd moet worden, omdat MySQL daarvoor is gebouwd. Maar hoe zit het dan met het plaatsen van indexen over kleine tabellen met 2 rijen

[P_de_B]

Heb je al eens gekeken met Explain? Je hebt geindentificeerd dat er een query traag is, met explain krijg je daar nog meer detailinformatie van. Ik kan me voorstellen dat een index op het veld status niet selectief genoeg is.

[DataGhost]

Ik denk dat het belangrijkste is dat je laat zien of, misschien beter, inziet waardoor de queries langzaam zijn. Dat Jet Profiler zegt dat je query langzaam is zegt me niet zo heel veel, zegt 'ie ook waarom je query langzaam is of alleen maar dat 'ie langzaam is? Ik ben niet gigantisch bekend met de interne werking van MySQL maar de query die jij laat zien zou gewoon de index op status moeten gebruiken, dunkt me. Zeker met maar 12000 rijen zou dat nooit 'lang' mogen duren, tenzij er verder ook flink wat meuk in je config verkeerd staat zodat de harde schijf gebruikt moet worden bijvoorbeeld.

Anyway, om een niet zo lang verhaal korter te maken: doe eens EXPLAIN op de query die je liet zien (en eventueel andere), post de output hier en kijk er ondertussen zelf ook even naar. Dan hebben wij in ieder geval ook een beter beeld van wat er precies gebeurt.

edit:
ohai P_de_B

Trouwens, posts 'normaliseren' door de status eruit te halen is op papier misschien nuttig maar in de praktijk heb je geen enkele winst qua opslag (zelfs verlies) aangezien je status-veld even groot is als je id. If anything zal het volgens mij langzamer gaan.

[ Voor 13% gewijzigd door DataGhost op 10-11-2011 08:50 ]

[RSD]

Hier de gevraagde output:

1 SIMPLE m ALL nicheid,profileid 17698 Using where
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 db.m.profileid 1 Using index

De query is:

SELECT COUNT(*) AS totalitems FROM media m JOIN profile p ON (p.id=m.profileid) WHERE m.online='YES' AND m.nicheid<>18
--------

Ja, daarom had ik ook zoiets van dat het normaliseren niet echt nodig is, omdat ik net zo goed een Boolean veld kan aanmaken met 0 of 1. Maar waar plaats ik de index dan op? Enkel op dat status veld? Of kan ik de tabel dan beter opsplitsen in 2 tabellen, 1 voor online en 1 voor offline, zodat ik die Where clausule er niet in heb en hoe zit dat dan met het uitsluiten van een rubriek.

[ Voor 45% gewijzigd door RSD op 10-11-2011 09:11 ]

[RSD]

Als ik trouwens het volgende doe:

SELECT COUNT(*) AS totalitems FROM media m WHERE m.online='YES'

dan is de join size ook nog 17685

en als ik:

SELECT COUNT(*) AS totalitems FROM media m

doe, dan staat er dat de join zise very good is, vandaar ook dat ik dacht om het op te splitsen.

Nu moet ik zeggen dat heel af en toe de status op offline staat.

[T-MOB]

Een index op een boolean veld gaat je niets helpen. Met maar twee mogelijkheden wordt je index nooit sneller. Een boolean moet je dan wel opslaan als BOOL (TINYINT(1)). String vergelijkingen (yes/no) zijn een bak trager.

Afhankelijk van de gebruikte database-engine zijn er wel wat dingen te optimaliseren. MyISAM houdt rowcounts bij voor tabellen. SELECT COUNT(*) FROM `table` is een shortcut voor het ophalen van die rowcount. Met een WHERE-clause zal MyISAM rijen moeten tellen wat altijd trager is. InnoDB slaat geen rowcount op voor een tabel. InnoDB moet dus altijd rijene tellen.

Als er rijen geteld moeten worden kun je MySQL forceren om een index te gebruiken. De COUNT(*)-query kun je dan beter zo schrijven: SELECT COUNT(`primary`) FROM `table` WHERE `primary` > 0; (aangenomen dat primary altijd groter dan 0 is...).

Hoe dan ook kan ik me met 12000 rijen niet voorstellen dat het traag is. Hoe traag is "traag" dan volgens de metingen die je gedaan hebt?

[Camulos]

RSD schreef op donderdag 10 november 2011 @ 09:08:
De query is:

SELECT COUNT(*) AS totalitems FROM media m JOIN profile p ON (p.id=m.profileid) WHERE m.online='YES' AND m.nicheid<>18

Zoals T-MOB al zegt.. wat count je precies? Wellicht is de JOIN helemaal niet nodig?
Hoe groot zijn beide tabellen (profile/media) en wat is hun structuur?

[GlowMouse]

T-MOB schreef op donderdag 10 november 2011 @ 10:47:
Een index op een boolean veld gaat je niets helpen. Met maar twee mogelijkheden wordt je index nooit sneller. Een boolean moet je dan wel opslaan als BOOL (TINYINT(1)). String vergelijkingen (yes/no) zijn een bak trager.

covering index is het toverwoord.

Als er rijen geteld moeten worden kun je MySQL forceren om een index te gebruiken. De COUNT(*)-query kun je dan beter zo schrijven: SELECT COUNT(`primary`) FROM `table` WHERE `primary` > 0; (aangenomen dat primary altijd groter dan 0 is...).

Daarvoor is FORCE INDEX.

Posts tellen is altijd een kwestie van denormalisatie: sla dat aantal ergens op en werk het bij als er een post bijkomt.

Ik zie heel veel queries zonder duidelijke tabeldefinities, gebruikte engine, etc. Dan valt er 0.0 over te zeggen.