[Mysql] myisam tabel traag

[herminator]

Ik heb een database tabel in myisam formaat met 260000 records.
Ik gebruik een volgende query in php om alle berichten van vandaag te laten zien.
Hier zit geen join in.

code:

$sql = "select id, berichtid, tijd, categorie, titel from berichten 
WHERE date_format(tijd, '%Y-%m-%d') = '$vandaag' 
ORDER BY tijd Desc";

Het probleem dat optreedt is als volgt.

Als er een tijdje (half uurtje?) niemand de php pagina op heeft gevraagd duurt het een paar minuten voordat er eindelijk een resultaat is. Na 30 sec krijg je dus ook die standaard php error dat het te lang duurt en dus na 30 sec stopt.
Als eenmaal de pagina toch werkt na een paar minuten is de volgende nieuwe refresh er binnen een seconde.
Heeft dit misschien met caching te maken? Zoeken en dergelijke in berichten gaat dan gewoon snel. Totdat het weer een tijdje niet meer wordt gebruit,

Er zit een index op de tekstvelden van de tabel.
Dit is nodig om de fulltext search functie van mysql te gebruiken.
Elke minuut komen er 1 - 3 nieuwe berichten bij. Het is net alsof hij de nieuwe berichten gaat zitten indexeren voordat de php pagina eindelijk geladen wordt. Hoe langer je dus wacht hoe meer berichten er in de database bij zijn gekomen en hoe langer het lijkt te duren.

Het draait op IIS php 5.0.3 en mysql 5.0

Server heeft 1 gig werkgeheugen (niet vol) en een 2.4 intel xeon cpu

[ Voor 16% gewijzigd door herminator op 11-09-2007 09:43 ]

[T_E_O]

Hoe is de tabel gedefinieerd ? (show create table berichten;)

Klinkt alsof er geen indexes gemaakt zijn. Of misschien kan de index niet gebruikt worden doordat je er eerst date_format() overheen hangt.

[T-MOB]

Voor berichten van vandaag kun je ook de volgende syntax gebruiken:

SQL:

DATE(`tijd`) == CURDATE()

Dat scheelt allicht iets voor MySQL.
Verder ben ik met T_E_O dat je waarschijnlijk een index op `tijd` mist...

[ Voor 7% gewijzigd door T-MOB op 11-09-2007 10:02 . Reden: Mjs, moet je wel juiste voorbeelden posten... ]

[herminator]

Dit is de tabel structuur

code:

CREATE TABLE `berichten` (
  `berichtid` int(11) NOT NULL auto_increment,
  `id` varchar(255) NOT NULL,
  `tijd` datetime NOT NULL,
  `categorie` varchar(255) NOT NULL,
  `titel` tinytext NOT NULL,
  `subtitel` tinytext,
  `body` longtext NOT NULL,
  `auteur` varchar(255) default NULL,
  `teller` int(11) NOT NULL,
  PRIMARY KEY  (`berichtid`),
  FULLTEXT KEY `categorie` (`categorie`,`titel`,`subtitel`,`body`,`auteur`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=270920 ;

Index gegevens

Formatteren dynamisch
Collatie latin1_swedish_ci
Rijen 267.515
Lengte van de rij ø 1.577
Grootte van de rij ø 2.873 Bytes
Volgende Autoindex 270.920
Gecreëerd 12 Feb 2007 om 16:25
Laatst bijgewerkt 11 Sept 2007 om 09:55 Ik zal vananvond kijken wanneer deze bijgewerkt is voordat ik de php pagina opvraag.)
Laatst gecontroleerd 16 Mar 2007 om 23:10

[ACM]

Je doet een functie op een veldwaarde van je tabel. MySQL kan dan geen index gebruiken en zal al je records af moeten gaan...

Met een query zoals die van T-MOB zou ie wel een index op tijd kunnen gebruiken als je die gedefinieerd had.

[FragFrog]

Zoals gezegd, je hebt geen index op je tijd column, maar dat is je grootste probleem niet.

Het grootste probleem is dat jij 260000 tijdconversies en stringvergelijkingen gaat uitvoeren. Dat duurt wel even ja.

MySQL kan prima met een datum rekenen, maak daar dan ook gebruik van! Dus niet "WHERE date_format(tijd, '%Y-%m-%d') = '$vandaag' " maar "WHERE `tijd` >= CURDATE()"

Afaik heeft MyIsam trouwens inderdaad wat extra tabelinformatie waardoor al eerder uitgevoerde queries snel opnieuw opgevraagd kunnen worden, maar dat is slechts een patchoplossing

//edit
ACM was me weer voor

[ Voor 3% gewijzigd door FragFrog op 11-09-2007 10:11 ]

[herminator]

Ik heb de curdate() er nu ingezet.
Het lijkt me dat het probleem zich nu alleen voor gaat doen als je op een eerdere datum gaat zoeken?

Die index op het datum veld kan ik alsnog aanmaken door in phpmyadmin op index te klikken?

[ACM]

FragFrog schreef op dinsdag 11 september 2007 @ 10:09:
Afaik heeft MyIsam trouwens inderdaad wat extra tabelinformatie waardoor al eerder uitgevoerde queries snel opnieuw opgevraagd kunnen worden, maar dat is slechts een patchoplossing

Buiten de normale OS disk cache en MyISAM's key cache heeft MyISAM geen bijzondere zaken vziw. Wel heeft MySQL zelf nog de zogenaamde query cache, waar resultaten van queries die precies zo eerder uitgevoerd zijn in opgeslagen kunnen worden. Overigens werkt die functie niet met dynamische functies zoals curdate, dus dan moet je wel bij het opstellen van de query zelf een geschikte datum er in mikken om er in dit geval gebruik van te kunnen maken.

Uiteraard heeft het toevoegen van de index en het overboord schoppen van die functies op het tijdveld veel meer effect.

[ACM]

herminator schreef op dinsdag 11 september 2007 @ 10:17:
Ik heb de curdate() er nu ingezet.
Het lijkt me dat het probleem zich nu alleen voor gaat doen als je op een eerdere datum gaat zoeken?

Het staat je natuurlijk vrij om de query zodanig aan te passen dat je een datumbereik gebruikt, bijvoorbeeld
where tijd >= '2007-09-10 00:00:00' and tijd < '2007-09-11 00:00:00'

als je gisteren wilt weten. Ook hier is een index op tijd bij nuttig.

Die index op het datum veld kan ik alsnog aanmaken door in phpmyadmin op index te klikken?

Je hebt kans dat ie dat niet snel genoeg uit kan voeren, phpmyadmin is tenslotte ook een php-applicatie... Maar er is altijd nog de command line interface natuurlijk.

[FragFrog]

Zal ik daar wel mee in de war zijn ACM, het is hier al eens eerder voorbijgekomen dat MyISAM key cache heeft (waardoor counts ed veel sneller gaan dan met innoDB), dacht dat query cache ook engine specifiek was

herminator: lees je eens in in de MySQL handleiding, afhankelijk van wat je wil met datums in het verleden kun je bijvoorbeeld met date_sub of interval al een eind komen:

SQL:

WHERE DATE('tijd') = CURDATE() - INTERVAL 4 DAYS

[ Voor 8% gewijzigd door FragFrog op 11-09-2007 10:30 ]

[herminator]

Bedankt voor jullie antwoorden. Nu weet ik dat het aan die datumfuntie ligt.
Phpmyadmin haalt het inderdaad niet om de index te maken omdat het te lang duurt.
Hier kom ik wel uit denk ik op de commandline.