Jeffrey L. Taylor wrote in post #958953:
> How should I index the terms table for maximum speed?
How can we tell you? You neglected to say how you're using that
table...or is the query below the only one you're interested in?
> It doesn't have
> to be
> Rails migration doable.
But it will be, since adding indices generally is.
> E.g. a primary key of (user_id, article_id,
> fnv) is
> okay. fnv is a 63 bit Fowler-Noll-Vo hash.
>
>
> def self.neighbors(user_id, article_id)
> sql = "SELECT t1.article_id, SUM(t1.freq * t2.freq) AS cosim FROM "
> \
> "tokens AS t1 JOIN tokens AS t2 " \
> "ON t1.fnv = t2.fnv WHERE t1.user_id = #{user_id} AND t2.user_id =
> #{user_id} AND " \
> "t1.scoring = 1 AND t2.scoring = 0 AND t2.article_id = #{article_id}
> GROUP BY t1.article_id " \
> "ORDER BY cosim DESC LIMIT 3"
> connection.select_rows(sql)
> end
Run EXPLAIN SELECT on this query (or whatever your DB's equivalent is).
See where it's doing full table scans and add indices as appropriate.
>
>
> TIA,
> Jeffrey
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
