Gathering of Tweakers

quote:

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)

quote:

Piete 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

quote:

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

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

Piete wijzigde dit bericht 05-07-2008 14:06 (51%)

quote:

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'

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?

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<?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

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.

Noork wijzigde dit bericht 05-07-2008 14:56 (8%)

quote:

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.

Piete wijzigde dit bericht 05-07-2008 14:44 (8%)

quote:

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)

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

quote:

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:

quote:

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.

quote:

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

Piete wijzigde dit bericht 05-07-2008 17:23 (108%)

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

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.

MaxxMark wijzigde dit bericht 05-07-2008 18:54 (3%)
Reden: url toegevoegd

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.

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.

quote:

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

Dat zeg ik.

quote:

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.

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

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.

FYI:
SQL:

1	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

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.

quote:

Omega007 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.

quote:

Piete 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.

quote:

Piete 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.

quote:

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.

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?

quote:

Piete 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.

quote:

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