Changeset 106 for devel/content

Show
Ignore:
Timestamp:
01/12/06 08:02:45 (3 years ago)
Author:
sven
Message:

some sql optimisation
- count(*) is more efficient than equivalent count(fieldname) of primary key
- unlefting left joins where the right is required
- "a IN (x,y)" is easier to optimise than "a=x OR a=y"

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • devel/content/mainindex/content_loggedout.php

    r45 r106  
    77        $run_result .= "<p>". sprintf(gettext("<a href=\"%s\">Find others</a> with similar interests and goals."), url . "search/tags.php") . "<br /><br />"; 
    88         
    9         $users = db_query("SELECT distinct users.*, icons.filename as iconfile FROM tags LEFT JOIN users ON users.ident = tags.owner left join icons on icons.ident = users.icon WHERE (tags.tagtype = 'biography' OR tags.tagtype = 'minibio' OR tags.tagtype = 'interests')AND users.icon != -1 AND tags.access = 'PUBLIC' and users.user_type = 'person' ORDER BY rand( ) LIMIT 3 "); 
     9        $users = db_query("SELECT DISTINCT users.*, icons.filename AS iconfile FROM tags JOIN users ON users.ident = tags.owner LEFT JOIN icons ON icons.ident = users.icon WHERE tags.tagtype IN ('biography','minibio','interests') AND users.icon != -1 AND tags.access = 'PUBLIC' AND users.user_type = 'person' ORDER BY RAND() LIMIT 3"); 
    1010         
    1111        if (sizeof($users) > 0) { 
    …… 
    3030        $run_result .= "<p>&nbsp;</p>"; 
    3131         
    32         $news = db_query("select weblog_posts.* from weblog_posts left join users on users.ident = weblog_posts.weblog where users.username = 'news' order by posted desc limit 1"); 
     32        $news = db_query("select weblog_posts.* from weblog_posts join users on users.ident = weblog_posts.weblog where users.username = 'news' order by posted desc limit 1"); 
    3333        if (sizeof($news) > 0) { 
    3434                 

  • devel/content/mainindex/content_main_index.php

    r84 r106  
    77        $run_result .= "<p>". gettext("Tell people about yourself and connect to others with similar interests and goals.") . "<br />"; 
    88         
    9         $users = db_query("SELECT distinct users.*, icons.filename as iconfile FROM tags LEFT JOIN users ON users.ident = tags.owner left join icons on icons.ident = users.icon WHERE (tags.tagtype = 'biography' OR tags.tagtype = 'minibio' OR tags.tagtype = 'interests')AND users.icon != -1 AND tags.access = 'PUBLIC' and users.user_type = 'person' ORDER BY rand( ) LIMIT 3 "); 
     9        $users = db_query("SELECT DISTINCT users.*, icons.filename AS iconfile FROM tags JOIN users ON users.ident = tags.owner LEFT JOIN icons ON icons.ident = users.icon WHERE tags.tagtype IN ('biography','minibio','interests') AND users.icon != -1 AND tags.access = 'PUBLIC' AND users.user_type = 'person' ORDER BY RAND() LIMIT 3"); 
    1010         
    1111        if (sizeof($users) > 0) { 
    …… 
    3131        $run_result .= "<p>&nbsp;</p>"; 
    3232         
    33         $news = db_query("select weblog_posts.* from weblog_posts left join users on users.ident = weblog_posts.weblog where users.username = 'news' order by posted desc limit 1"); 
     33        $news = db_query("select weblog_posts.* from weblog_posts join users on users.ident = weblog_posts.weblog where users.username = 'news' order by posted desc limit 1"); 
    3434        if (sizeof($news) > 0) { 
    3535