[livejournal] r19283: LJSUP-9102. Need to remove very slow que...

LJSUP-9102. Need to remove very slow queries.

U   trunk/cgi-bin/LJ/Browse.pm
U   trunk/cgi-bin/LJ/Vertical.pm

Modified: trunk/cgi-bin/LJ/Browse.pm
===================================================================
--- trunk/cgi-bin/LJ/Browse.pm	2011-06-15 04:26:30 UTC (rev 19282)
+++ trunk/cgi-bin/LJ/Browse.pm	2011-06-15 04:47:51 UTC (rev 19283)
@@ -957,38 +957,41 @@
     my @entries = ();
     my $comm_list = join ",", @$comms;
     my $dbh = LJ::get_db_reader();
-    if (bytes::length($search)) {
-        my $where = $vertical ? " AND km.vert_id = " . $vertical->vert_id . " AND " : "";
-        my @search_words = map { "SELECT '%".$_."%' AS cond" } split /\s+/, $search;
-        $search = join " UNION ALL ", @search_words;
+
+    if (my @search_words = split /\s+/, $search) {
+        ## Search for keyword ids for search string
+        my $where_search = join " OR ", map { " keyword LIKE '%".$_."%' " } @search_words;
+        my $keyword_ids = $dbh->selectall_arrayref (
+                                "SELECT kw_id FROM vertical_keywords WHERE $where_search",
+                                { Slice => {} }
+                          );
+
+        ## No results
+        return () unless scalar @$keyword_ids;
+
+        ## Map keyword ids to community or journal posts
+        my $ph = join ",", map { '?' } @$keyword_ids;
+        my @vals = map { $_->{kw_id} } @$keyword_ids;
+        my $where = $vertical ? " AND km.vert_id = " . $vertical->vert_id : "";
         my $posts = $dbh->selectall_arrayref (
-            "SELECT journalid, jitemid
-                FROM vertical_keymap km
-                WHERE 
-                $where
-                kw_id IN (
-                    SELECT kw_id
-                        FROM vertical_keywords kw
-                        WHERE EXISTS (
-                            SELECT 1 
-                                FROM (
-                                    $search
-                                ) c 
-                            WHERE kw.keyword LIKE cond
-                        )
-                    )",
-            { Slice => {} }
-        ) || [];
+                                "SELECT * FROM vertical_keymap WHERE kw_id IN ($ph) $where", 
+                                { Slice => {} }, 
+                                @vals
+                          ) || [];
+
+        ## No results
+        return () unless scalar @$posts;
+
+        ## Load found posts ordered by timecreate
         my @found_posts = ();
         foreach my $post (@$posts) {
+            next unless grep { $post->{journalid} eq $_ } @$comms;
             my $post_ids = $dbh->selectall_arrayref (
-                "SELECT journalid, jitemid 
+                "SELECT journalid, jitemid, UNIX_TIMESTAMP(timecreate) AS timecreate
                     FROM category_recent_posts 
-                    WHERE journalid IN ($comm_list) 
-                        AND journalid = ? 
+                    WHERE journalid = ? 
                         AND jitemid = ? 
                         AND is_deleted = 0 
-                    ORDER BY timecreate DESC 
                     LIMIT $limit", 
                 { Slice => {} }, $post->{journalid}, $post->{jitemid}
             );
@@ -1006,6 +1009,7 @@
             }
             map { LJ::Entry->new ($_->{journalid}, jitemid => $_->{jitemid}) }      ## Create LJ::Entry object
             grep { $_->{journalid} }                                                ## remove SEO posts
+            sort { $b->{'timecreate'} <=> $a->{'timecreate'} }
             @found_posts;
     } else {
         if ($comm_list) {

Modified: trunk/cgi-bin/LJ/Vertical.pm
===================================================================
--- trunk/cgi-bin/LJ/Vertical.pm	2011-06-15 04:26:30 UTC (rev 19282)
+++ trunk/cgi-bin/LJ/Vertical.pm	2011-06-15 04:47:51 UTC (rev 19283)
@@ -369,7 +369,7 @@
 
     return $comms unless $comm_interests && @$comm_interests;
 
-    my $comms = $dbh->selectcol_arrayref (
+    $comms = $dbh->selectcol_arrayref (
         "SELECT userid
             FROM comminterests
             WHERE intid in (" . join (",", @$comm_interests) . ")",