From f1c01f9ead4a5f4da7c6964f0998831fa31f8a16 Mon Sep 17 00:00:00 2001
From: Brion Vibber <brion@pobox.com>
Date: Thu, 1 Apr 2010 10:15:40 -0700
Subject: [PATCH] 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(-)

diff --git a/classes/Profile.php b/classes/Profile.php
index 5de35c191a..54f557ea7c 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;
-- 
2.39.2