On Nov 4, 12:56 am, "Jeffrey L. Taylor" <r...@abluz.dyndns.org> wrote:
> Quoting Marnen Laibow-Koser <li...@ruby-forum.com>:
>
> > 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)
>
you could try an index on user_id,scoring, and you definitely want one
on token (any time you do a join you want an index on the columns you
join on). You might try adding article_id to that first index to see
if you can get the DB to use that to help by the grouping
Fred
> 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 rubyonrails-t...@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
