From 9f2cfd20f80fbeadd59b0f9a153be0b9f0799fae Mon Sep 17 00:00:00 2001
From: Brion Vibber <brion@pobox.com>
Date: Thu, 1 Apr 2010 10:15:40 -0700
Subject: Temporary hack until notice_profile_id_idx is updated to (profile_id,
 id) instead of (profile_id, created, id). It's been falling back to PRIMARY
 instead, which is really very inefficient for a profile that hasn't posted in
 a few months. Even though forcing the index will cause a filesort, it's
 usually going to be better. Even for large profiles it seems much faster than
 the badly-indexed query.

---
 classes/Profile.php | 63 +++++++++++++++++++++++++++++++++++++++--------------
 1 file changed, 47 insertions(+), 16 deletions(-)

(limited to 'classes/Profile.php')

diff --git a/classes/Profile.php b/classes/Profile.php
index 5de35c191..54f557ea7 100644
--- a/classes/Profile.php
+++ b/classes/Profile.php
@@ -225,31 +225,62 @@ class Profile extends Memcached_DataObject
     {
         $notice = new Notice();
 
-        $notice->profile_id = $this->id;
+        // Temporary hack until notice_profile_id_idx is updated
+        // to (profile_id, id) instead of (profile_id, created, id).
+        // It's been falling back to PRIMARY instead, which is really
+        // very inefficient for a profile that hasn't posted in a few
+        // months. Even though forcing the index will cause a filesort,
+        // it's usually going to be better.
+        if (common_config('db', 'type') == 'mysql') {
+            $index = '';
+            $query =
+              "select id from notice force index (notice_profile_id_idx) ".
+              "where profile_id=" . $notice->escape($this->id);
+
+            if ($since_id != 0) {
+                $query .= " and id > $since_id";
+            }
 
-        $notice->selectAdd();
-        $notice->selectAdd('id');
+            if ($max_id != 0) {
+                $query .= " and id < $max_id";
+            }
 
-        if ($since_id != 0) {
-            $notice->whereAdd('id > ' . $since_id);
-        }
+            $query .= ' order by id DESC';
 
-        if ($max_id != 0) {
-            $notice->whereAdd('id <= ' . $max_id);
-        }
+            if (!is_null($offset)) {
+                $query .= " LIMIT $limit OFFSET $offset";
+            }
+
+            $notice->query($query);
+        } else {
+            $index = '';
 
-        $notice->orderBy('id DESC');
+            $notice->profile_id = $this->id;
 
-        if (!is_null($offset)) {
-            $notice->limit($offset, $limit);
+            $notice->selectAdd();
+            $notice->selectAdd('id');
+
+            if ($since_id != 0) {
+                $notice->whereAdd('id > ' . $since_id);
+            }
+
+            if ($max_id != 0) {
+                $notice->whereAdd('id <= ' . $max_id);
+            }
+
+            $notice->orderBy('id DESC');
+
+            if (!is_null($offset)) {
+                $notice->limit($offset, $limit);
+            }
+
+            $notice->find();
         }
 
         $ids = array();
 
-        if ($notice->find()) {
-            while ($notice->fetch()) {
-                $ids[] = $notice->id;
-            }
+        while ($notice->fetch()) {
+            $ids[] = $notice->id;
         }
 
         return $ids;
-- 
cgit v1.2.3-54-g00ecf