0

I have a Item and Vector table:

CREATE TABLE Item (
  itemID INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(256)
);

CREATE TABLE Vector (
  itemID INT REFERENCES Item(itemID),
  dim INT,
  value FLOAT
);

So for instance, if item 3 has vector <4,2,5> we have (3, 0, 4), (3,1,2), (3,2,5) in Vector table. (In reality, it is not 3 dimensional, can be really big)

Now given specific vector V=<v_1, v_2, v_3>, I want to SELECT 10 Items with smallest cosine distance to vector V.

I am struggling to find the way to send the vector V to the SQL Server.

Please help me!

I am using MySQL on Ubuntu

Jin Sakuma
  • 21
  • 2
  • The data structure is not clear. for your example, please describe the data set - which table contains which rows – David דודו Markovitz Nov 20 '16 at 07:27
  • To send the vector, either insert it into a temporary table (or the same table if you add a column `temp`), or directly use it in your query, e.g. `select * from (select 0 as dim, v_1 as value union select 1, v_2 union select 2, v_3) as vec left join where ` (`v_x` being the actual values). If you want to pass the vector to a procedure, you can pass it as a varchar (e.g. `$v1+','+$v2+','+$v3+...`) and split it again using `substring_index`. I'm glad that that's the only part you are struggling with, because the rest is the way harder part of this problem. – Solarflare Nov 20 '16 at 12:24

0 Answers0