Optimizing a connection request to extract data from A with a condition on B sorted by B

Question

I have an element-to-element similarity matrix configured with these tables:

items (id, ...) (Primary key `id`)
similarities (item1_id, item2_id, similarity) (Index on `item1_id` and `item2_id`)

The tables similaritiescontain pairs of identifiers with a similarity index, i.e.

item1_id  item2_id  similarity
1         2         0.3143
2         3         0.734

For efficient storage, “return pairs” are omitted, i.e. there is only one pair (1,2), there is no excess pair (2,1). This means that the foreign key for an element can be either item1_idor item2_id.

Now I want to find items that look like a bunch of other items, sorted in descending order. I am using this query:

SELECT    `Item`.*
FROM      `items` AS `Item`
LEFT JOIN `similarities` AS `Similarity`
       ON (`Item`.`id` = `Similarity`.`item1_id`
              AND `Similarity`.`item2_id` IN (1, 2, 3, ...))
          OR (`Item`.`id` = `Similarity`.`item2_id`
              AND `Similarity`.`item1_id` IN (1, 2, ,3, ...))
WHERE     `Similarity`.`item1_id` IN (1, 2, 3, ...)
          OR `Similarity`.`item2_id` IN (1, 2, 3, ...)
GROUP BY  `Item`.`id`
ORDER BY  `Similarity`.`similarity` desc

It is very slow, although it takes 4-5 seconds for ~ 100,000 items and ~ 30,000 similarity pairs. ADDITION seems to be extremely costly. Here's the EXPLAINed request :

select_type  table       type         possible_keys      key                key_len  ref   rows    Extra
SIMPLE       Similarity  index_merge  item1_id,item2_id  item1_id,item2_id  110,110  NULL  31      Using sort_union(item1_id,...
SIMPLE       Item        ALL          PRIMARY            NULL               NULL     NULL  136600  Using where; Using join buffer

, ? , JOIN-, .

Answer 1

, , , , . , UNION () id, similarities, .

SELECT Item.*, s.other_item_id, s.similarity
FROM items AS Item
JOIN
    (
    SELECT item1_id AS id, item2_id AS other_item_id, similarity FROM similarities
    UNION
    SELECT item2_id AS id, item1_id AS other_item_id, similarity FROM similarities
    ) AS s ON s.id = items.id
WHERE items.id IN (1, 2, 3, ...)
ORDER BY s.similarity DESC;

similarities JOIN, WHERE.

Answer 2

, , . SELECT PDEDO--ish, , CASE ...

SELECT    
CASE WHEN `Item2`.`id` IS NULL THEN 
  `Item1`.`id`
ELSE `Item2`.`id`
END,

SELECT    
CASE WHEN `Item2`.`id` IS NULL THEN 
  `Item1`.`name`
ELSE `Item2`.`name`
END,

SELECT    
CASE WHEN `Item2`.`id` IS NULL THEN 
  `Item1`.`description`
ELSE `Item2`.`description`
END,

[and so on]

FROM      `items` AS `Item1`
LEFT OUTER JOIN `similarities` AS `Similarity`
       ON (`Item1`.`id` = `Similarity`.`item1_id`
RIGHT OUTER JOIN `items` AS `Item2`
       ON (`Item2`.`id` = `Similarity`.`item2_id`       
WHERE     `Similarity`.`item1_id` IN (1, 2, 3, ...)
          OR `Similarity`.`item2_id` IN (1, 2, 3, ...)
ORDER BY  `Similarity`.`similarity` desc

Answer 3

Thanks to the inspiration, I received this request:

SELECT `Item`.*
FROM `items` AS `Item`
JOIN (
    SELECT `item1_id` AS `id`, `similarity`
    FROM   `similarities`
    WHERE  `similarities`.`item2_id` IN (1, 2, 3, ...)
    UNION
    SELECT `item2_id` AS `id`, `similarity`
    FROM   `similarities`
    WHERE  `similarities`.`item1_id` IN (1, 2, 3, ...)
) AS `SimilarityUnion` ON `SimilarityUnion`.`id` = `Item`.`id`
GROUP BY `SimilarityUnion`.`id`
ORDER BY `SimilarityUnion`.`similarity` DESC