Re: Enforce Symmetric Matrix

On Wed, May 07, 2014 at 04:07:22PM -0500, John McKown wrote:
> On Wed, May 7, 2014 at 3:36 PM, Randy Westlund <rwestlun(at)gmail(dot)com> wrote:
>
> > I have a table with N (over 35k) rows. I need to compare each of
> > those elements to every other element in the list, which is the
> > handshake problem.
> >
> > This results in a symmetric matrix (an adjacency matrix for an
> > undirected graph). Because all relations are symmetric, I only need to
> > store the upper triangle of this matrix. A ~ B and B ~ A are the same.
> >
> > I need some advice on how to implement this.
>
> Just a crazy idea, but have you considered something like the below:
>
> create view view1 as select id1 as v_id1, id2 as v_id2, value from abovetbl
> union select id2, id1, value from abovetbl;
>
> The above basically "expands" the "small matrix" table to have the same
> values that the "full matrix" table would have. because id1, id2 has the
> same "value" as id2, id1 .
>
> You can now create an index on "view1" like:
> create index view1_index on view1(v_id1);
>
> To find all the values for "B", you can now:
> select * from view1 where v_id1='B';
>
> The view does not take up much space. But the index on the view might. But
> only about as much space as an index on the "full matrix" table would. Oh,
> I changed the column names in the view so that it would hopefully not be as
> confusing has having multiple "id1" and "id2" column names with different
> meanings.
>
> I'm not a real heavy, but this may be of some use.
> --
> There is nothing more pleasant than traveling and meeting new people!
> Genghis Khan
>
> Maranatha! <><
> John McKown

This was exactly what I needed, thank you. Everything is working now :)