com web/master: Replaced the old SQL with a much more memory and time efficient single LEFT JOIN query and only fetching one row at a time.:
fetch/user-notes.php
| From: | Sherif Ramadan | Date: | Tue, 25 Dec 2012 02:31:56 +0000 |
| Subject: | com web/master: Replaced the old SQL with a much more memory and time efficient single LEFT JOIN query and only fetching one row at a time.: fetch/user-notes.php |
||
| Groups: | php.webmaster | ||
| Request: | Send a blank email to php-webmaster+get-15138@lists.php.net to get a copy of this message | ||
Commit: 1deb32e96917a5d9486971db4dd6ed528d10d7fc
Author: Sherif Ramadan <googleguy@php.net> Mon, 24 Dec 2012 21:31:56 -0500
Parents: 810fea593b33c8e967925e1923d0df3201d3b7af
Branches: master
Link: http://git.php.net/?p=web/master.git;a=commitdiff;h=1deb32e96917a5d9486971db4dd6ed528d10d7fc
Log:
Replaced the old SQL with a much more memory and time efficient single LEFT JOIN query and only
fetching one row at a time.
Changed paths:
M fetch/user-notes.php
Diff:
diff --git a/fetch/user-notes.php b/fetch/user-notes.php
index b24ecd5..6e3adab 100644
--- a/fetch/user-notes.php
+++ b/fetch/user-notes.php
@@ -8,24 +8,25 @@ if (!isset($_REQUEST['token']) || md5($_REQUEST['token']) !=
"19a3ec370affe2d899
try {
$dbh = new PDO('mysql:host=localhost;dbname=phpmasterdb', 'nobody',
'');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+ $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
} catch (PDOException $e) {
// Old error handling was to simply exit. Do we want to log anything here???
exit;
}
try {
- $query = "SELECT DISTINCT id,note.sect,user,note,UNIX_TIMESTAMP(ts) AS ts,";
- $query .= "IF(votes=0, 10, rating/votes) AS rate";
+ $query = "SELECT DISTINCT note.id,note.sect,note.user,note.note,UNIX_TIMESTAMP(note.ts)
AS ts,";
+ $query .= "SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down,";
+ $query .= "ROUND((SUM(votes.vote) / COUNT(votes.vote)) * 100) AS rate";
$query .= " FROM note";
+ $query .= " LEFT JOIN (votes) ON (note.id = votes.note_id)";
//Only select notes that have been approved
- $query .= " WHERE status is NULL";
- $query .= " ORDER BY sect,rate DESC,ts DESC";
+ $query .= " WHERE note.status is NULL";
+ $query .= " GROUP BY note.id";
+ $query .= " ORDER BY note.sect,ts DESC";
$stmt = $dbh->prepare($query);
$stmt->execute();
- $resultset = $stmt->fetchAll(PDO::FETCH_ASSOC);
-
- $getvotes = $dbh->prepare("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) -
SUM(votes.vote)) AS down FROM votes WHERE votes.note_id = ?");
} catch (PDOException $e) {
// Old error handling was to simply exit. Do we want to log anything here???
exit;
@@ -33,8 +34,9 @@ try {
// Print out a row for all notes, obfuscating the
// email addresses as needed
-foreach ($resultset as $row) {
+while($row = $stmt->fetch(PDO::FETCH_ASSOC) {
$user = $row['user'];
+ $row['rate'] = empty($row['rate']) ? 0 : $row['rate'];
if ($user != "php-general@lists.php.net" && $user !=
"user@example.com") {
if (preg_match("!(.+)@(.+)\.(.+)!", $user)) {
$user = str_replace(array('@', '.'), array(' at ', '
dot '), $user);
@@ -42,17 +44,7 @@ foreach ($resultset as $row) {
} else {
$user = '';
}
- // Calculate the votes for each note here
- try {
- $getvotes->execute(array($row['id']));
- $votes = $getvotes->fetch(PDO::FETCH_ASSOC);
- if ($votes === false) {
- $votes = array('up' => 0, 'down' => 0);
- }
- } catch(PDOException $e) {
- $votes = array('up' => 0, 'down' => 0);
- }
// Output here
echo "$row[id]|$row[sect]|$row[rate]|$row[ts]|$user|",
-
base64_encode(gzcompress($row['note'],3)),"|$votes[up]|$votes[down]\n";
+ base64_encode(gzcompress($row['note'],3)),"|$row[up]|$row[down]\n";
}