1

I have a given topic and I want to retrieve its similar topics based on topic keywords.

The schema is as follows:

CREATE TABLE `topic_features` (
  `topic_id` int(11) NOT NULL,
  `feature_id` int(11) NOT NULL,
  `weight` float DEFAULT '0',
  PRIMARY KEY (`topic_id`,`feature_id`),
  KEY `feature_id_cover_idx` (`feature_id`,`topic_id`,`weight`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

topic_features table contains topic keywords and their weights

Given a topic id I follow this scenario :

  • I retrive the top 50 keywords of the given topic ordered by weight.
  • Then I retrieve all the topics sharing at least one keyword of the previous keywords.
  • Calculate the similarity between each topic and the given one, based on the common keywords between the two topics, the formula is as follows :

SIMILARITY(t,tx) = SUM_FOR_EACH_COMMON_FEATURE(feature_weight(t) * feature_weight(tx)) where tx and t share a feature

The query I use currently and which is performming bad is :

SELECT
  tf1.topic_id,
  SUM(tf1.weight * tf2.weight) similarity
FROM topic_features tf1
  JOIN
  (
    -- get only top 50 features for the current topic
    SELECT
      feature_id,
      weight
    FROM topic_features
    WHERE topic_id = 124897
    ORDER BY weight DESC
    LIMIT 50
  ) tf2
  USING (feature_id)
 GROUP BY tf1.topic_id
  ORDER BY similarity DESC
LIMIT 20;

Query Explain Plan

+----+-------------+---------------------+------+----------------------+----------------------+---------+----------------+-----------+---------------------------------+

| id | select_type | table               | type | possible_keys        | key                  | key_len | ref            | rows      | Extra                           |

+----+-------------+---------------------+------+----------------------+----------------------+---------+----------------+-----------+---------------------------------+

|  1 | PRIMARY     | <derived2>          | ALL  | NULL                 | NULL                 | NULL    | NULL           |        50 | Using temporary; Using filesort |

|  1 | PRIMARY     | tf1                 | ref  | feature_id_cover_idx | feature_id_cover_idx | 4       | tf2.feature_id |         5 | Using index                     |

|  2 | DERIVED     | topic_features      | ALL  | PRIMARY              | PRIMARY              | 4       |                | 462221416 | Using where; Using filesort     |

+----+-------------+---------------------+------+----------------------+----------------------+---------+----------------+-----------+---------------------------------

Is there a way to improve the performance of this query? I was thinking about creating another table which will hold the sum of the weights between each two topics and which will be updated periodically, but I don't know if it is a good idea as I have about 2M topics and 300M topic_features and the update will take a lot of time.

  • 3
    Add an index on (topic_id, weight, feature_id) – ypercubeᵀᴹ Nov 21 '16 at 21:39
  • Thanks @ypercubeᵀᴹ, after adding the index the query execution time was reduced to about 2-3 seconds which is good but still not satistfied, When I checked the execute plan the index works only for the subquery part of the query. Is there a way to optimize the external part? – Yacine al Nov 22 '16 at 12:05
  • You could also replace SUM(tf1.weight * tf2.weight) with tf1.weight * SUM(tf2.weight) but I doubt you'll see too much of a gain. – ypercubeᵀᴹ Nov 22 '16 at 13:23
  • 1
    Other than that, it's not much you can do I think. The query should use the 2 composite indexes but it has to do the aggregation and is probably doing that with quite a lot of rows. Playing with buffer sizes might help. – ypercubeᵀᴹ Nov 22 '16 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.