[php/mysql] Optimaliseren query voor priveberichten systeem

[Verwijderd]

Hallo,

Ik heb op mijn site een priveberichten systeem. Er staan momenteen 225.000 records in en heet een afmeting van 60 mb.

Nu hebben wij de volgende database opbouw:

MySQL:

CREATE TABLE `priveberichten` (
  `berichtid` int(4) unsigned NOT NULL auto_increment,
  `titel` varchar(50) NOT NULL,   
  `datum` datetime NOT NULL,
  `bericht` text NOT NULL,
  `van` mediumint(3) unsigned NOT NULL,
  `naar` mediumint(3) unsigned NOT NULL,
  `gelezen` enum('0','1') default '0',
  `ip` varchar(20) default NULL,
  `type` tinyint(1) default NULL,
  `melden` tinyint(1) default NULL,
  PRIMARY KEY  (`berichtid`),
  KEY `van` (`van`),
  KEY `naar` (`naar`),
  KEY `type` (`type`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

De query die we draaien om de privenberichten op te halen van een bepaald persoon is:

PHP:

        $getPrive = $oDB->query("
            SELECT 
                berichtid, van, titel, gelezen, DATE_FORMAT(datum, '%d-%m-%y %H:%i') AS verzonden 
            FROM 
                priveberichten 
            WHERE 
                naar = " . MEMBERID . "
            AND 
                type = 0 
            ORDER BY 
                berichtid 
            DESC
        ") or db_error(mysqli_error($oDB));

Explain geeft het volgende van bovenstaande query:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE priveberichten ref naar,type naar 3 const 782 Using where; Using filesort


Nu duurt het ophalen van een bericht toch zeker een dikke seconden wanneer ik er 550 op moet halen uit de database.

Hoe zou ik bovenstaande query en tabel kunnen optimaliseren? Zover ik weet ben ik al redelijk ver met het optimaliseren maar volgens mij zou het sneller kunnen.

Iemand die mij tips kan geven mbt bovenstaande info.

Piete

[ Voor 6% gewijzigd door Verwijderd op 05-07-2008 12:35 ]

[Voutloos]

Probeer wat samengestelde indexen om van die filesort af te komen.

[dev10]

Welke indexen heb je op je tabel staan? Afgelopen week heb door een paar goed geplaatste query's de uitvoertijd van een simpele query terug gebracht van 700 ms naar 4ms.

[Verwijderd]

Momenteel heb ik:
KEY `van` (`van`),
KEY `naar` (`naar`),
KEY `type` (`type`)

[Marcj]

Een key is niet hetzelde als een index. Trouwens, welke database is dit? Want ik ken wel een PRIMARY KEY en FOREIGN KEY, maar geen KEY. Probeer anders gewoon iets als:

code:

CREATE INDEX `IX_naar` ON `priveberichten` (`naar`)

[WouZz]

Voor zover ik heb begrepen kan mysql maar 1 index per tabel gebruiken.

Voor de WHERE clause heeft ie een index op 'naar' nodig, voor het sorteren een index op 'berichtid'.
Wat je dus moet doen is een gecombineerde index op deze colommen leggen.

[Marcj]

WouZz schreef op zaterdag 05 juli 2008 @ 13:12:
Voor zover ik heb begrepen kan mysql maar 1 index per tabel gebruiken.

Voor de WHERE clause heeft ie een index op 'naar' nodig, voor het sorteren een index op 'berichtid'.
Wat je dus moet doen is een gecombineerde index op deze colommen leggen.

Hoezo? Zie: http://dev.mysql.com/doc/refman/5.0/en/indexes.html

Er moeten sowieso 16 indices per tabel ondersteund worden.

[Morax]

Marcj schreef op zaterdag 05 juli 2008 @ 13:22:
[...]

Hoezo? Zie: http://dev.mysql.com/doc/refman/5.0/en/indexes.html

Er moeten sowieso 16 indices per tabel ondersteund worden.

Maar MySQL kan maar 1 index per query gebruiken

[Verwijderd]

Database is mysql met mysiam tabel en die KEY krijg ik wanneer ik op INDEX plaatje druk in phpmyadmin.

Ik heb nu een aantal dingen getest:

Index op naar
Phrasetijd 0.6881 sec. @ 16.45 Kb/s
Phrasetijd 0.6886 sec. @ 16.44 Kb/s
Phrasetijd 0.6968 sec. @ 16.25 Kb/s

Index op naar en van
Phrasetijd 0.6398 sec. @ 17.7 Kb/s
Phrasetijd 0.6735 sec. @ 16.81 Kb/s
Phrasetijd 0.7345 sec. @ 15.41 Kb/s

Maar volgens mij moet dit best sneller kunnen. Hoe kan je die gecombineerde INDEX maken van berichtid en naar op de manier zoals paar post hierboven gezegt werd?

[ Voor 10% gewijzigd door Verwijderd op 05-07-2008 13:28 ]

[WouZz]

Marcj schreef op zaterdag 05 juli 2008 @ 13:22:
[...]

Hoezo? Zie: http://dev.mysql.com/doc/refman/5.0/en/indexes.html

Er moeten sowieso 16 indices per tabel ondersteund worden.

Ja, maar hij gebruikt er maar 1.. (tot aan versie 5.0)

[WouZz]

Verwijderd schreef op zaterdag 05 juli 2008 @ 13:27:
...

Maar volgens mij moet dit best sneller kunnen. Hoe kan je die gecombineerde INDEX maken van berichtid en naar op de manier zoals paar post hierboven gezegt werd?

PhpMyAdmin: tabel structuur -> create index on x columns

of via sql: http://dev.mysql.com/doc/refman/4.1/en/create-index.html

[crisp]

WouZz schreef op zaterdag 05 juli 2008 @ 13:12:
Voor de WHERE clause heeft ie een index op 'naar' nodig

Op 'naar' en 'type' zelfs

[Verwijderd]

Volgens het voorbeeld op mysq.com heb ik nu het volgende gedaan:

CREATE INDEX IX_naar ON priveberichten (naar, type);
en
CREATE INDEX IX_van USING BTREE ON priveberichten (van, type);

Heeft helaas nog weinig effect laadtijd.

Niewe EXPLAIN:

Generatie Tijd: 05 Jul 2008 om 14:06
Gegenereerd door: phpMyAdmin 2.11.6 / MySQL 5.0.45-community
SQL-query: EXPLAIN SELECT berichtid, van, titel, gelezen, DATE_FORMAT(datum, '%d-%m-%y %H:%i') AS verzonden FROM priveberichten WHERE naar = 1 AND type = 0 ORDER BY berichtid DESC ;
Rijen: 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE priveberichten ref IX_naar IX_naar 5 const,const 656 Using where; Using filesort

[ Voor 51% gewijzigd door Verwijderd op 05-07-2008 14:06 ]

[WouZz]

crisp schreef op zaterdag 05 juli 2008 @ 13:38:
[...]

Op 'naar' en 'type' zelfs

Je hebt dus een index nodig op 'naar', 'type' en 'berichtid'

[Noork]

Is je query langzaam of je verbinding? Want ik zie Kb/s erbij staan? Is je Mysql db niet gewoon traag, hoe zit het met gebeugengebruik e.d., aantal open connecties e.d.? Heb je op een andere db getest? Is het een shared hosting of eigen server?

[Verwijderd]

Is je query langzaam of je verbinding?
Naar mijn idee is de query langzaam aangezien de pagina al geladen tot waar de query komt. Dit duurt dan toch bijna een seconden.

Want ik zie Kb/s erbij staan?
Geeft het scriptje mee waarmee ik de laadtijd bepaal

Is je Mysql db niet gewoon traag, hoe zit het met gebeugengebruik e.d., aantal open connecties e.d.?
Onbekend

Heb je op een andere db getest?
Nee werkt nu gewoon op mysql met tabel type mysiam heb InnoDB geprobeerd maar had het idee dat die nog langzamer werd.

Is het een shared hosting of eigen server?
Eisen server

Ik heb de volgende tabel opbouw:

PHP:

CREATE TABLE `priveberichten` (
  `berichtid` int(4) unsigned NOT NULL auto_increment,
  `titel` varchar(50) NOT NULL,
  `datum` datetime NOT NULL,
  `bericht` text NOT NULL,
  `van` mediumint(3) unsigned NOT NULL,
  `naar` mediumint(3) unsigned NOT NULL,
  `gelezen` enum('0','1') default '0',
  `ip` varchar(20) default NULL,
  `type` tinyint(1) default NULL,
  `melden` tinyint(1) default NULL,
  PRIMARY KEY  (`berichtid`),
  KEY `IX_naar` (`naar`,`type`,`berichtid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Nieuwste EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE priveberichten ref IX_naar IX_naar 5 const,const 500 Using where

[Noork]

Kun je uitleggen wat 'KEY ...' is? Ik kan dat nergens terug vinden in de MySql documentatie.
mmh laat maar, is zeker een alias voor index.

Kun je de query niet b.v. testen op een testserver, developer pc, o.i.d. ( dus niet de live ) en dan met b.v. Navicat?

Een eigen server, maar niet bekend met aantal connecties, geheugengebruik e.d.? ...lijkt me dat je dat eerst eens uit moet zoeken.

Wa voor site gaat het eigenlijk om? Ik zie nl. dat je userid 3 karakters groot is, dus zou je max 999 gebruikers hebben. Dan hebben de gebruikertjes toch flink zitten te spammen Eigen server voor een website voor 999 gebruikers lijkt me ook wat overkill? Laat maar, me is net wakker.

[ Voor 8% gewijzigd door Noork op 05-07-2008 14:56 ]

[Verwijderd]

Ik zie nl. dat je userid 3 karakters groot is, dus zou je max 999 gebruikers hebben. Dan hebben de gebruikertjes toch flink zitten te spammen Eigen server voor een website voor 999 gebruikers lijkt me ook wat overkill?

Waar baseer je dat op? Mediumint 3?

Mediumint 3 http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html kan waarden bevatten van 0 tot 16777215 lijkt mij wel voldoende?

Die KEY geeft die in de phpmyadmin weer als ik er een INDEX op geplaatst heb.

[ Voor 8% gewijzigd door Verwijderd op 05-07-2008 14:44 ]

[Duroth]

Waar baseer je dat op? Mediumint 3?

Mediumint 3 http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html kan waarden bevatten van 0 tot 16777215 lijkt mij wel voldoende?

Niet wanneer je de lengte van je MEDIUMINT gaat beperken tot 3 chars. Dan zal je nooit boven 999 komen.

(Het getal 16777215 is 8 karakters lang, en past dus nooit in een veld met een lengte van 3, ongeacht het type veld)

[Verwijderd]

Weet je dat heel zeker aangezien er getallen instaan van meer dan 6000?

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

Hier staat wat het aantal tekens inhoud:

This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces.

The display width does not constrain the range of values that can be stored in the column

[ Voor 108% gewijzigd door Verwijderd op 05-07-2008 17:23 ]

[Verwijderd]

probeer deze 2 eens:
ALTER TABLE `priveberichten` ADD INDEX `IX_test1` ( `naar` , `type` , `berichtid` )
ALTER TABLE `priveberichten` ADD INDEX `IX_test2` ( `naar` , `type` )

[MaxxMark]

De 3 in mediumint(3) heeft niets te maken met de opslag van getallen. Het heeft wel alles temaken met de weergave er van. Indien er 3 staat betekent dat je kolom weergave minimaal 3 breed is. Indien er dan een getal kleiner dan 100 in staat pad hij hem met spaties. dus: " 9" of " 20" (notice de 1 en 2 spaties). Indien het getal groter dan 999 wordt zullen er gewoon extra karakters weergegeven worden dus: "1234". Zie eventueel ook "Numeric Data Types" in het MySQL 5 handboek. Waarschijnlijk is het ook wel online te vinden. (edit: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html - zie onder het tabelletje)

Ik maak nergens op of de laatste gecombineerde index van 3 kolomen sneller is geworden of niet. Index technisch kan het zover ik zie nie meer sneller. Je moet inderdaad een gecombineerde index hebben op de 3 kolommen (2 voor de WHERE x AND y) en de 3e voor de ORDER BY.

Sowieso zie ik in je query geen limit staan. Dit betekent dat de gehele lijst over gestuurd zal worden vanaf de DB naar de php client. Als je toch maar 10 resultaten wilt laten zien is het een waste of bandwith dat je de 490 resterende items ook over stuurt. Uit je laatste explain maak ik namelijk op dat hij 500 resultaten heeft gevonden. Wel zie ik dat je netjes je select statement hebt beperkt, dit voorkomt ook nutteloos bandwith verbruik.

[ Voor 3% gewijzigd door MaxxMark op 05-07-2008 18:54 . Reden: url toegevoegd ]

[Alain]

Ik heb eens even wat geprobeerd en ik merk een verschil tussen een index op 'naar, type, berichtid' of een index op alleen 'naam'. Een index alleen op naam is ongeveer 25% sneller hier. Ik merk trouwens geen noemenswaardige vertraging bij een resultset beneden de 50.000 berichten (1s).

Ik heb verder getest bij 500.000 berichten voor 1 persoon. De grootste vertraging zit hem blijkbaar in het sorteren. Als ik een index op 'naar' afdwing zit ik op 6s en ik zit op 3,5s als ik de index forceer op 'berichtid'. Queries meerdere malen getest om een eerlijk resultaat te krijgen.

[Verwijderd]

Mijn cunclusie is dan toch wel dat ik hem met:

ALTER TABLE `priveberichten` ADD INDEX `IX_test1` ( `naar` , `type` , `berichtid` )

het snelst de resultaten ophaal. Blijkbaar houdt het hier bij op. Het idee achter geen LIMIT is berichten ouder dan 14 dagen worden vanzelf verwijderd.

[Voutloos]

Verwijderd schreef op zaterdag 05 juli 2008 @ 20:59:
ALTER TABLE `priveberichten` ADD INDEX `IX_test1` ( `naar` , `type` , `berichtid` )

Dat zeg ik.

Het idee achter geen LIMIT is berichten ouder dan 14 dagen worden vanzelf verwijderd.

Als je maar wel al deze rijen wil hebben. Als je in code bepaalt dat je er maar 10 wil is het nogal zonde.

Met goede indexen (en die heb je in ieder geval voor deze query), moet het ook wel kunnen met soft-deletes of een datum where clause, zodat je niet meer steeds alles hoeft te verwijderen. Continu je historie verwijderen is echt suf.

[Verwijderd]

Hij verwijderd 1 maal daags in de nacht berichten ouder dan 14 dagen dus lijkt mij geen probleem.

[Alain]

Dat is niet waar Voutloos op doelt. Als je 500 records opvraagt en er maar 10 laat zien, dan communiceer je 490 records voor Jan Lul van de server naar de client. Dat kost relatief veel tijd.

[Bozozo]

FYI:

SQL:

SELECT * FROM SomeTable LIMIT 20, 10;

Geeft rij 21 tm 30 terug. Basic stuff maar het scheelt een hoop maffe queries voor sufferds zoals ik die over dit soort dingen heen hadden gelezen tijdens de tutorials

[Verwijderd]

Ja dat snap ik wel, maar snap niet waarom ik het zou moeten toepassen? Dan kan ik niet alle records laten zien tenzij ik een navigatie systeem eronder zet.

[Creepy]

Verwijderd schreef op zaterdag 05 juli 2008 @ 17:32:
probeer deze 2 eens:
ALTER TABLE `priveberichten` ADD INDEX `IX_test1` ( `naar` , `type` , `berichtid` )
ALTER TABLE `priveberichten` ADD INDEX `IX_test2` ( `naar` , `type` )

Die tweede index op naar en type is overbodig omdat daarvoor ook de index op naar, type en berichtid kan worden gebruikt.

[Noork]

Verwijderd schreef op zondag 06 juli 2008 @ 11:11:
Ja dat snap ik wel, maar snap niet waarom ik het zou moeten toepassen? Dan kan ik niet alle records laten zien tenzij ik een navigatie systeem eronder zet.

500 berichten op 1 pagina is toch ook helemaal niet overzichtelijk? Een volgende/vorige scriptje is snel gemaakt. Kijk b.v. naar PEAR Pager.

[Soultaker]

Verwijderd schreef op zaterdag 05 juli 2008 @ 14:43:
[MEDIUMINT(3)] kan waarden bevatten van 0 tot 16777215 lijkt mij wel voldoende?

MEDIUMINT kan waarden bevatten tot 2²⁴; in een normale database geeft het getalletje erarchter wel degelijk aan hoeveel cijfers er opgeslagen worden, en kun je dus geen getal boven de 999 representeren. MySQL negeert dat soort beperkingen en dus kun je er inderdaad grotere getallen in opslaan, maar dan kun je die (3) in je tabeldefinitie net zo goed weglaten (tenzij het padden tot drie karakters, wat MySQL dan weer wel doet, belangrijk is). Die 3 zorgt er in ieder geval niet voor dat er 3 bytes voor de opslag gebruikt worden; dat is al wat MEDIUMINT doet.

Creepy schreef op zondag 06 juli 2008 @ 11:33:
Die tweede index op naar en type is overbodig omdat daarvoor ook de index op naar, type en berichtid kan worden gebruikt.

Juist, en dat geld ook voor eventuele keys op kolommen. In het algemeen heeft het geen zin om twee keys te hebben waarbij de ene een prefix is van de andere; dan kun je de eerste achterwege laten.

[Verwijderd]

Ook raar dat ze dit dan aan hun laars lappen! Maar het is neem ik aan toch wel beter om een TINYINT (Unsigned) te gebruiken wanneer je toch niet boven de 255 zal komen?

[Noork]

Verwijderd schreef op zaterdag 05 juli 2008 @ 14:29:
Is je Mysql db niet gewoon traag, hoe zit het met gebeugengebruik e.d., aantal open connecties e.d.?
Onbekend

Heb je dit al uitgezocht? Lijkt me niet onbelangrijk.

Heb je op een andere db getest?
Nee werkt nu gewoon op mysql met tabel type mysiam heb InnoDB geprobeerd maar had het idee dat die nog langzamer werd.

Ik doel meer op een andere machine. Wel gewoon Myisam, maar dan op je eigen pc bijvoorbeeld.

Maakt het nog uit als je sorteert op datum ipv berichtid? Wat doet de query met een limit? Test eens, limit 10, 50, 100, 200 enz