Re: appropriate database design and query

[hector vass]

Not sure joining tables like this is the right approach...

Interesting problem got suckered into playing around with this.. but first had to create some data so excuse the length of post (most of which is creating the data) but you might want to try this...

create database iv;
drop table if exists iv.rowmultiplier;
create table iv.rowmultiplier (x int(8) unsigned not null);
insert into iv.rowmultiplier values (1),(1),(1),(1),(1),(1),(1),(1);
insert into iv.rowmultiplier select a.x from iv.rowmultiplier a,iv.rowmultiplier b,iv.rowmultiplier c,iv.rowmultiplier d,iv.rowmultiplier e;

drop table if exists iv.images;
create table iv.images
            (
              imageid int(8) unsigned not null auto_increment primary key,
              junk char(1)
            );
 
insert into iv.images (junk)
select
            'x'
from iv.rowmultiplier
limit 40000;
 
drop table if exists iv.vectors;
create table iv.vectors
            (
                        imageid int(8) unsigned not null,
                        vectorid smallint(4) unsigned not null auto_increment,
                        vvalue smallint(4) unsigned not null,
                        primary key id(imageid,vectorid)
            );

insert into iv.vectors (imageid,vvalue)
select
            b.imageid,
            round(rand(876)*1000) as vvalue
from iv.images a inner join iv.images b
where a.imageid<=200;
alter table iv.vectors add index (vectorid);
select count(*) from iv.vectors; 

drop table if exists iv.example;
create table iv.example
select
            vectorid,vvalue
from iv.vectors
where imageid=1;

select
            imageid,
            sum(abs(a.vvalue-b.vvalue)) as vdiff
from iv.vectors a inner join iv.example b using(vectorid)
group by imageid
oorder by vdiff asc
limit 10;