[PHP] m'n forum moet sneller

[Verwijderd]

Haaj luitjes
kheb 2 problemen.



1e probleem:

Ik wil in mijn forum een topicindex, waarbij van elk topic de auteur & timestamp van de eerste post maar ook die van de laatste post worden vermeldt. Zoiets als:

Aardappelen zijn lekker [gepost door baz op 1 jan 02 - laatste post door piet op 21 mrt 02]

Wat ik nu heb:

code:

SELECT topics.*, posts.*, users.*
FROM topics, posts, users
WHERE topics.forumid = [een_forumid] AND topics.lastpostid = posts.postid
AND posts.userid = users.userid
ORDER BY posts.timestamp;

Het probleem hier is dat je nu per topic informatie krijgt over de laatste topic (de auteur en de timestamp daarvan), en dat je in PHP in het loopje dat dit alles op het beeld zet per forum nog even zult moeten querien naar info over de eerste post in het topic.

Dit kost tijd... en best wel veel ook, geloof ik.



2e probleem:

Dan nog een probleem van optimaliserende aard =]. In mijn forum index staat bij elk forum het aantal posts dat er in gedaan is. Het stomme is echter dat dit een bottleneck blijkt als et forum drukker wordt; hij doet namelijk elke keer een count in elk topic in dat forum. Iets van

code:

SELECT COUNT(posts.postid)
FROM posts, topics
WHERE posts.topicid = topics.topicid
AND topics.forumid = [een_forumid];

Dit duurt iets van .2 seconden per forum op mijn site. (Dwz. als je mn hele hoofdpagina wil laden duurt het ongeveer 2 seconde)
Op zich valt het nog een beetje mee, maar als mijn forum strax 10x zo groot is bijvoorbeeld duurt het dus ook 10 keer zo lang. Is hier een andere manier voor?



Nou ik hoop dat ik een beetje logisch en duidelijk was want ik heb alweer veeeeel te lang niet geslapen

[Fatamorgana]

Als je MySQL gebruikt zou je voor elke query even EXPLAIN kunnen zetten (in phpMyAdmin). Dan kun je zien of je queries wel geoptimized zijn en of je misschien nog wat indexen beter kunt zetten. Kan de snelheid verhogen als alles nog niet optimaal staat.

En je zou ook andere tabellen erbij kunnen zetten met de info die je nu elke keer bij elkaar moet zoeken uit verschillende tabellen. Dan worden de search queries veel simpeler. 1 select uit 1 tabel. Je hebt dan wel redundante info in wat tabellen zitten, maar het is wel veel sneller, dus dat geeft niet.

[Grum]

het is overduidelijk dat je geen indices hebt ...

met goede indices duren queries op honderduizende records nog onder de seconde

dus ik zou zeggen maar indices aan en geniet van de speed

(indices komen btw et best tot hun recht op een minimaal 3NF database)

[Verwijderd]

Op zondag 31 maart 2002 08:37 schreef Baz_ het volgende:

code:

SELECT COUNT(posts.postid)
FROM posts, topics
WHERE posts.topicid = topics.topicid
AND topics.forumid = [een_forumid];

Kan dit niet zo?

code:

SELECT COUNT(posts.postid)
FROM posts, topics
WHERE posts.topicid = topics.topicid
GROUP BY posts.topicid

[justmental]

Dit zijn typisch de zaken die je redundant opslaat bij een forum.
laatste post-aantal posts etc.
Zelf sla ik dit redundant op op topic en forumniveau.
Volgens mij doen alle goeie pakketten het zo (ook topix).

Dus bij het posten de redundante gegevens bijwerken in de topic en forum tabel.

[ACM]

Op zondag 31 maart 2002 08:37 schreef Baz_ het volgende:
Wat ik nu heb:

code:

SELECT topics.*, posts.*, users.*
FROM topics, posts, users
WHERE topics.forumid = [een_forumid] AND topics.lastpostid = posts.postid
AND posts.userid = users.userid
ORDER BY posts.timestamp;

Ik zie 3x een .* dat kan/moet efficienter, ik gok dat je die gegevens niet allemaal nodig hebt en dan iig niet de topicid's, userid's etc dubbel...

Select * is in de regel een slechte gewoonte.

[El Martino]

Die 3x select is idd niet echt snel, waarom gebruik je geen joins???

[D2k]

Op zondag 31 maart 2002 12:56 schreef ACM het volgende:

[..]

Ik zie 3x een .* dat kan/moet efficienter, ik gok dat je die gegevens niet allemaal nodig hebt en dan iig niet de topicid's, userid's etc dubbel...

Select * is in de regel een slechte gewoonte.

jajja daar ist tie weer
voorvechter van het Front "select * is ranzig en traag"

code:

test verslag
query1:
    $query="
        SELECT 
            id,
            topicid,
            userid,
            text,
            timestamp 
        from 
            forum_posts
    ";
query2:
    $query="
        SELECT 
            *
        from 
            forum_posts
    ";
    
aantal herhalingen:
25

uitgevoerd op www.happyfun.nl
aantal records: 6100

rest code:
    $result=mysql_query($query);
    while ($item = mysql_fetch_row($result))
    {
        //print_r($item);
        //echo "<br>";
    }   

resultaat: (3x ter referentie en vergelijk)

q1: 24.997175 21.859552 18.958795 |gemiddeld: 21.9385
q2: 35.081239 32.112438 31.418007 |gemiddeld: 32.9705

conclusie:
op 25 x 6100  records een verschil van  11.58 seconden!!!!!

[Verwijderd]

Op zondag 31 maart 2002 13:09 schreef D2k het volgende:

.. bizar ..

Kan iemand mij dat uileggen? Ik dacht namelijk ook dat '*' trager was (hetzij veel, hetzij weinig), maar 12 seconden sneller.. dat is toch.. TE veel?

[D2k]

Op zondag 31 maart 2002 13:26 schreef DiEana het volgende:

[..]

Kan iemand mij dat uileggen? Ik dacht namelijk ook dat '*' trager was (hetzij veel, hetzij weinig), maar 12 seconden sneller.. dat is toch.. TE veel?

simpel
je geeft op WAT ie moet selecteren en das altijd sneller dan dat je zegt "ga alles maar halen"

[Verwijderd]

Op zondag 31 maart 2002 13:28 schreef D2k het volgende:

[..]

simpel
je geeft op WAT ie moet selecteren en das altijd sneller dan dat je zegt "ga alles maar halen"

fuck, fuck.. ik ben zo te zien nog niet lang wakker

ik had q1 en q2 verwisseld, en leefde dus in de waan dat * sneller was

ach, vrolijk pasen verder, sorry

[Verwijderd]

kzal het vanavond ff goed doorlezen, moet nu weg; bedankt voor de reacties iig!

enne die select * was ff omdat ik te lui was om de juiste dingen neer te pleuren ... maar dat was uiteindelijk natuurlijk wel de bedoeling

cu lat0r

[Verwijderd]

zow... doorgelezen

ok

ik ben depressief.

want uhm.. nu moet ik redundant gaan opslaan en dat botert niet met mijn idee van logica en efficientie

:'(:'(

maar goed tnx iig

[Verwijderd]

Op zondag 31 maart 2002 11:34 schreef Grum het volgende:
het is overduidelijk dat je geen indices hebt ...

met goede indices duren queries op honderduizende records nog onder de seconde

dus ik zou zeggen maar indices aan en geniet van de speed

(indices komen btw et best tot hun recht op een minimaal 3NF database)

HIJG!!! TNX!!!
heb nu een paar tactische indices gezet: forum index duurde eerst ca. 2 seconden, nu nog maar .2
whoei! kijk daar word ik nou weer wat blijer van op de late avond

[Dennis]

Overigens blijkt uit het feit dat je dit topic opent dat je geen goed databasemodel hebt

[Verwijderd]

Wil je het nog sneller ?? Hou het aantal posts bij per thread en per forum... kost je wel wat meer ruimte maar bespaard een hoop tijds verlies met die counts..

[bartvb]

Aangezien ik ongeloofelijk eigenwijs ben heb ik D2K's test maar ff herhaald:

Select all: 12.883355021477
Select only ID: 3.0391980409622

met deze code:

code:

function getmicrotime(){
   list($usec, $sec) = explode(" ",microtime());
   return ((float)$usec + (float)$sec);
}

mysql_connect('localhost', '***', '***');
mysql_select_db('***');

$start = getmicrotime();
for($i=0; $i < 100; $i++)
{  
   $sql = "
    SELECT *
    FROM posts
    WHERE post_id < 5000
    ";
   mysql_query($sql);
}  
$time = getmicrotime()-$start;

print "Select all: $time <br />\n";

$start = getmicrotime();
for($i=0; $i < 100; $i++)
{
   $sql = "
    SELECT post_id
    FROM posts
    WHERE post_id < 5000
    ";
   mysql_query($sql);
}  
$time = getmicrotime()-$start;
 
print "Select only ID: $time <br />\n";

 
?>

Dit alles op een posts tabel met 522272 rows. Hij lijkt dus nog gelijk te hebben ook

[D2k]

Op maandag 01 april 2002 20:47 schreef bartvb het volgende:
Aangezien ik ongeloofelijk eigenwijs ben heb ik D2K's test maar ff herhaald:

tssssssssssssssssssssssssssk

[..testzut..]
Select all: 12.883355021477
Select only ID: 3.0391980409622
[..]
Dit alles op een posts tabel met 522272 rows. Hij lijkt dus nog gelijk te hebben ook

uiteraard

[bartvb]

On second thought.. Maybe not

Select all: 11.471279978752
Select only ID: 2.2420719861984
Select post_id and forum_id: 10.686872959137

zodra er dus 2 fields geselecteerd worden maakt het bijna niets uit of je * doet of niet. Hmm. weird.

BTW dit is de table:

code:

+-----------------+-----------------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra      |
+-----------------+-----------------------+------+-----+---------+----------------+
| post_id      | mediumint(8) unsigned |    | PRI | NULL    | auto_increment |
| topic_id    | mediumint(8) unsigned | | MUL | 0    |          |
| forum_id    | smallint(5) unsigned  | | MUL | 0    |          |
| poster_id  | mediumint(8)     |   | MUL | 0    |          |
| post_time  | int(11)         |    | MUL | 0    |          |
| poster_ip  | varchar(8)       |   |     |    |            |
| enable_sig    | tinyint(1)        |   |     | 1    |          |
| post_username   | varchar(30)      | YES  |     | NULL    |           |
| enable_bbcode   | tinyint(1)      |   |     | 1    |          |
| enable_html     | tinyint(1)      |   |     | 0    |          |
| enable_smilies  | tinyint(1)      |   |     | 1    |          |
| post_edit_time  | int(11)        | YES  |     | NULL    |         |
| post_edit_count | smallint(5) unsigned  | |     | 0    |          |
+-----------------+-----------------------+------+-----+---------+----------------+

[D2k]

um volgens mij test je verkeerd?
select * vergelijken met select post_id, blaa moet je vergelijken

[Verwijderd]

Op maandag 01 april 2002 20:50 schreef bartvb het volgende:
On second thought.. Maybe not

Select all: 11.471279978752
Select only ID: 2.2420719861984
Select post_id and forum_id: 10.686872959137

zodra er dus 2 fields geselecteerd worden maakt het bijna niets uit of je * doet of niet. Hmm. weird.

BTW dit is de table:

code:

+-----------------+-----------------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra      |
+-----------------+-----------------------+------+-----+---------+----------------+
| post_id      | mediumint(8) unsigned |    | PRI | NULL    | auto_increment |
| topic_id    | mediumint(8) unsigned | | MUL | 0    |          |
| forum_id    | smallint(5) unsigned  | | MUL | 0    |          |
| poster_id  | mediumint(8)     |   | MUL | 0    |          |
| post_time  | int(11)         |    | MUL | 0    |          |
| poster_ip  | varchar(8)       |   |     |    |            |
| enable_sig    | tinyint(1)        |   |     | 1    |          |
| post_username   | varchar(30)      | YES  |     | NULL    |           |
| enable_bbcode   | tinyint(1)      |   |     | 1    |          |
| enable_html     | tinyint(1)      |   |     | 0    |          |
| enable_smilies  | tinyint(1)      |   |     | 1    |          |
| post_edit_time  | int(11)        | YES  |     | NULL    |         |
| post_edit_count | smallint(5) unsigned  | |     | 0    |          |
+-----------------+-----------------------+------+-----+---------+----------------+

offtopic, maar: hoe maak jij "zo snel" die mooie tabel? Ik wil dat ook: ga ik uitprinten, is makkelijker dan constant switchen

bedankt

[bartvb]

DiEana: dat kan ook via mail hoor
anyway, tiep eens:

describe posts;

op je MySQL commandline...

[D2k]

code:

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");    // Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
                                     // always modified
header("Cache-Control: no-store, no-cache, must-revalidate");  // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");               // HTTP/1.0

echo "Select *<br>";

function timing($command)
{  
    global $starttime, $endtime;    
    if($command == 'start') //starten    
    {   
        $mtime1 = microtime();   
        $mtime1 = explode(" ",$mtime1); 
        $mtime1 = $mtime1[1] + $mtime1[0];   
        $starttime = $mtime1;    
    }  
    elseif($command == 'end')  
    {    
        $mtime2 = microtime();    
        $mtime2 = explode(" ",$mtime2);    
        $mtime2 = $mtime2[1] + $mtime2[0];    
        $endtime = $mtime2;    
        $totaltime = ($endtime - $starttime);    
        $totaltime = round($totaltime,6);    
        return $totaltime;  
    }
}

//begin tijd
timing(start);

//code
//10000 keer
for($i=0;$i<25;$i++)
{
//select *

    $query="
        SELECT 
            *
        from 
            forum_posts
    ";

    $result=mysql_query($query);
    while ($item = mysql_fetch_row($result))
    {
        //print_r($item);
        //echo "<br>";
    }       
            
}
//einde code

//einde tijd
echo timing(end);

zo heb ik getest (2x dan ook 1 voor alle rijen)

[bartvb]

Om D2k blij te maken heb ik er ook nog een fetch tegenaan gegooid en select ik nu ook alle velden expliciet. Dit alles met dit resultaat:

Select all: 6.2309029102325
Select only post_id: 5.6858180761337
Select only forum_id: 5.3366879224777
Select all fields explicitly:: 9.3201059103012

code:

code:

<?php

function getmicrotime(){
   list($usec, $sec) = explode(" ",microtime());
   return ((float)$usec + (float)$sec);
}

function bench($sql, $comment)
{
   $start = getmicrotime();
   for($i=0; $i < 50; $i++)
   {
    $sql = "
       SELECT forum_id
       FROM posts
       WHERE post_id < 5000
       ";
    $result = mysql_query($sql);
    while ($item = mysql_fetch_row($result))
    {
       // niks
    }
   }
$time = getmicrotime()-$start;
   print "$comment: $time <br />\n";
}

mysql_connect('localhost', 'bartvb', 'bvbdb=');
mysql_select_db('bokt_nuke');

$sql = "
   SELECT *                                                                                 FROM posts
   WHERE post_id < 5000
   ";
bench($sql, "Select all");

$sql = "
   SELECT post_id
   FROM posts  
   WHERE post_id < 5000
   ";
bench($sql, "Select only post_id");

$sql = "
   SELECT forum_id
   FROM posts
   WHERE post_id < 5000
   ";
bench($sql, "Select only forum_id");

$sql = "
   SELECT 
    post_id,
    topic_id,
    poster_id,
    post_time,
    poster_ip,
    enable_sig,
    post_username,
    enable_bbcode,
    enable_html,
    enable_smilies,
    post_edit_time,
    post_edit_count
   FROM posts
   WHERE post_id < 5000
   ";
bench($sql, "Select all fields explicitly:");
?>

Dat is trouwens op een productie database (iets van 110 users die zitten te forumen). Heb het ook ff geprobeerd op een backup van die DB:

Select all: 13.172277927399
Select only post_id: 9.6108939647675
Select only forum_id: 14.717181921005
Select all fields explicitly:: 20.510131955147

wel zelfde server. Resultaten zijn dus niet al te betrouwbaar maar ik gok dat we dus wel kunnen concluderen dat de resultaten van D2K enigzinds twijfelachtig zijn

Maar ik moet toegeven dat * gewoon niet netjes is.. Of het in performance ook een merkbaar verschil geeft is een 2e

[Verwijderd]

Ik weet niet of het helemaal bij het onderwerp past maaruhh. Weet iemand een site waar je informatie kunt vind over database modellen en waar je naar moet kijken als je een database wilt maken? En dan in duidelijk taal voor 'n newbie zoals ik.

[Verwijderd]

Op maandag 01 april 2002 08:27 schreef ddc het volgende:
Overigens blijkt uit het feit dat je dit topic opent dat je geen goed databasemodel hebt

mjah dat zou serieus wel eens kunnen...

/me vraagt zich ook af wie hem eens even haarfijn kan uitleggen hoe een fatsoenlijk datamodel er uit ziet?

[dreambofh]

Vage verschillen.
Heb het ook eens getest mijn mijn SQL database maar geen verschillen.. vaaag....

Maar als je over een tijdje een super groot forum denkt te hebben is het misschien leuk om over te gaan nar PostgrSQL. Dit draaid goed onder Linux... Dit hebben we een tijdje geleden ook gedaan bij een grote database en dat heeft ECHT geholpen. De mensen klagen niet meer

[Crazy D]

Op maandag 01 april 2002 21:27 schreef bartvb het volgende:
Maar ik moet toegeven dat * gewoon niet netjes is.. Of het in performance ook een merkbaar verschil geeft is een 2e

Wat is merkbaar... 0.3 seconden is nauwelijks tot niet merkbaar als je gewoon ff je pagina opvraagt. Maar met duizenden gebruikers die dat tegelijkertijd doen......

En imho maakt het ook uit hoeveel velden je hebt en hoeveel je er op vraagt. SELECT * t.o.v. SELECT veld1, veltd2 etc. zal ook nauwelijks verschil maken als je 10 van de 11 velden opvraagt (en helemaal als die 11e een bitveld is of zo...), maar met een tabel met 40 velden, en je hebt er 10 nodig, zal het weer meer schelen. En 't is ook afhankelijk van wat voor extra (overbodige) velden je meeneemt in je query. 5 int velden is natuurlijk minder heavy dan 2 text velden die lekker goed gevuld zijn...