Quoting Marnen Laibow-Koser <[email protected]>:
> 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?
>
Correct, this is the one I care about. All others are trivial in terms of
resources compared to this one.
> > It doesn't have
> > to be
> > Rails migration doable.
>
> But it will be, since adding indices generally is.
>
Composite keys are not supported in stock Rails. And I will not switch DB
servers next week, so having the index creation in portable form is not a
necessity.
> > 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.
>
>
mysql> explain extended SELECT t1.article_id, SUM(t1.freq * t2.freq) FROM
tokens AS t1 JOIN tokens AS t2 ON t1.token = t2.token AND t1.user_id = 1 AND
t2.user_id = 1 AND t1.scoring = 1 AND t2.scoring = 0 GROUP BY article_id;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+----------------------------------------------+
| 1 | SIMPLE | t1 | ALL | user_id | NULL | NULL | NULL |
34773 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | t2 | ALL | user_id | NULL | NULL | NULL |
34773 | 100.00 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.07 sec)
mysql> show warnings;
| Level | Code | Message
| Note | 1003 | select `tv2sql_development`.`t1`.`article_id` AS
`article_id`,sum((`tv2sql_development`.`t1`.`freq` *
`tv2sql_development`.`t2`.`freq`)) AS `SUM(t1.freq * t2.freq)` from
`tv2sql_development`.`tokens` `t1` join `tv2sql_development`.`tokens` `t2`
where ((`tv2sql_development`.`t2`.`scoring` = 0) and
(`tv2sql_development`.`t1`.`scoring` = 1) and
(`tv2sql_development`.`t2`.`user_id` = 1) and
(`tv2sql_development`.`t1`.`user_id` = 1) and
(`tv2sql_development`.`t2`.`token` = `tv2sql_development`.`t1`.`token`)) group
by `tv2sql_development`.`t1`.`article_id` |
TIA,
Jeffrey
--
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.
