MySQL Forums
Forum List  »  Newbie

Re: appropriate database design and query
Posted by: hector vass
Date: January 15, 2009 03:10AM

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)
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)
            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
from iv.vectors
where imageid=1;

            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;

Options: ReplyQuote

Written By
Re: appropriate database design and query
January 15, 2009 03:10AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.