[dw-free] Better query, force the index

[mark]

[commit: http://hg.dwscoalition.org/dw-free/rev/a9e2c39d8b9d]

Better query, force the index

Thanks to brainstorming with exor674, it seems that forcing
the index is better here. Then we get the desired behavior and a fast query.

Patch by mark.

Files modified:

• cgi-bin/DW/Logic/LogItems.pm

--------------------------------------------------------------------------------
diff -r e1764db1ad9e -r a9e2c39d8b9d cgi-bin/DW/Logic/LogItems.pm
--- a/cgi-bin/DW/Logic/LogItems.pm	Thu Jun 14 23:36:31 2012 +0000
+++ b/cgi-bin/DW/Logic/LogItems.pm	Thu Jun 14 23:52:48 2012 +0000
@@ -615,12 +615,11 @@
     # get latest 10 entries that were commented on - we will see whether $remote can view them later
     # disregard screened and deleted comments when ordering
 
-    # NOTE: this used to have AND state NOT IN ('D', 'S') but that was removed because there is no
-    # index that works for the query in that case. it was taking a long time on large accounts and
-    # causing database unhappiness.
+    # NOTE: we have to force the index because MySQL's optimizer gets this wrong. we know that our
+    # data is going to be near the top.
     my $entryids = $u->selectcol_arrayref(
-        q{SELECT DISTINCT nodeid FROM talk2
-          WHERE journalid = ? 
+        q{SELECT DISTINCT nodeid FROM talk2 FORCE INDEX (PRIMARY)
+          WHERE journalid = ? AND state NOT IN ('D', 'S')
           ORDER BY jtalkid DESC LIMIT 10},
         undef, $u->id
     );
--------------------------------------------------------------------------------