cvs: php-master-web /manage users.php

johannes Fri Jul 3 19:40:31 2009 UTC Modified files: /php-master-web/manage users.php Log: Speed-up user search on master by - using SQL_CALC_ROW instead of searching two times (require MySQL 4.0) - not searching through user notes by default # Next speedup might be doing a SELECT userid FROM notes WHERE MATCH # first and adding a "OR userid IN(...found ids...)" to the current query # to get rid of the join http://cvs.php.net/viewvc.cgi/php-master-web/manage/users.php?r1=1.57&r2=1.58&diff_format=u Index: php-master-web/manage/users.php diff -u php-master-web/manage/users.php:1.57 php-master-web/manage/users.php:1.58 --- php-master-web/manage/users.php:1.57 Fri Apr 24 07:15:46 2009 +++ php-master-web/manage/users.php Fri Jul 3 19:40:31 2009 @@ -302,51 +302,52 @@ exit; } ?> -<table width="100%"> - <tr> - <td> - <a href="<?php echo "$PHP_SELF?username=$user";?>">edit your entry</a> - | <a href="<?php echo "$PHP_SELF?unapproved=1";?>">see outstanding requests</a> - </td> - <td align="right"> +<div> +<div style="float:right"> <form method="GET" action="<?php echo $PHP_SELF;?>"> <input type="text" name="search" value="<?php echo clean($search);?>" /> - <input type="submit" value="search"> + <input type="submit" value="search"><br> + <input type="checkbox" name="searchnotes" value="1" <?php echo isset($_GET['searchnotes']) ? 'checked="checked"' : ''?>> Search notes </form> - </tr> -</table> +</div> +<div> + <a href="<?php echo "$PHP_SELF?username=$user";?>">edit your entry</a> + | <a href="<?php echo "$PHP_SELF?unapproved=1";?>">see outstanding requests</a> +</div> +</div> <?php $begin = $begin ? (int)$begin : 0; $full = $full ? 1 : (!isset($full) && ($search || $unapproved) ? 1 : 0); $max = $max ? (int)$max : 20; +$searchnotes = !empty($_GET['searchnotes']); -$limit = "LIMIT $begin,$max"; -$orderby = $order ? "ORDER BY $order" : ""; - -$searchby = $search ? "WHERE (MATCH(name,email,username) AGAINST ('$search') OR MATCH(note) AGAINST ('$search') OR username = '$search')" : ""; -if (!$searchby && $unapproved) { - $searchby = 'WHERE (username IS NOT NULL AND NOT cvsaccess)'; +$query = "SELECT DISTINCT SQL_CALC_FOUND_ROWS users.userid,cvsaccess,username,name,email FROM users "; +if ($search) { + $where = "WHERE (MATCH(name,email,username) AGAINST ('$search') OR username = '$search') "; + if ($searchnotes) { + $query .= " LEFT JOIN users_note USING(userid) $where OR MATCH(note) AGAINST ('$search') "; + } else { + $query .= $where; + } +} else { + $query .= ' WHERE 1=1 '; } -elseif ($unapproved) { - $searchby .= ' AND (username IS NOT NULL AND NOT cvsaccess)'; + +if ($unapproved) { + $query .= ' AND (username IS NOT NULL AND NOT cvsaccess) '; } -$query = "SELECT DISTINCT COUNT(users.userid) FROM users"; -if ($searchby) - $query .= " LEFT JOIN users_note USING(userid) $searchby"; +if ($order) { + $query .= " ORDER BY $order "; +} +$query .= " LIMIT $begin,$max "; $res = db_query($query); -$total = mysql_result($res,0); - +#echo $query; -if ($searchby) { - $query = "SELECT DISTINCT users.userid,cvsaccess,username,name,email,note FROM users LEFT JOIN users_note USING (userid) $searchby group by userid $orderby $limit"; -} else { - $query = "SELECT DISTINCT users.userid,cvsaccess,username,name,email FROM users $orderby $limit"; -} +$res2 = db_query("SELECT FOUND_ROWS()"); +$total = mysql_result($res2,0); -#echo "<pre>$query</pre>"; -$res = db_query($query); $extra = array( "search" => stripslashes($search), @@ -355,6 +356,7 @@ "max" => $max, "full" => $full, "unapproved" => $unapproved, + "searchnotes" => (int)$search_notes, ); show_prev_next($begin,mysql_num_rows($res),$max,$total,$extra);