0

Im trying to solve a puzzle more info. So I have pieces and solutions created with those pieces. 

 Pieces: piece_id
 Solution: solution_id

If I have three pieces will have 8 solutions 2^3. My idea is use solution_id to indicate what pieces are part of it.

 solution_id   pieces 
      0          000  -- no pieces (not really a solution)
      1          001  -- only the piece_id = 1
      2          010  -- only the piece_id = 2
      3          011  -- have piece 1 and 2
      4          100
      5          101
      6          110
      7          111  -- all pieces (not really a solution because need solve two parts)

I will need two solutions, but solution2 cant have any pieces on solution1

The questions are:

  • Can I do bitwise operation between integers to know if two solutions share any pieces or need a bit array?
  • Can I use one index to improve the perfomance of this join?

  • There is a better way to do this?

    SELECT s1.solution_id, s2.solution_id
FROM solutions
WHERE s1.solution_id & s2.solution_id = 0
Community
  • 1
  • 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118

1 Answers1

0

You seem to know the answers to your questions:

  1. Yes, you can use bitwise operations.
  2. You probably cannot use indexes to optimize this query.

However, you could store the data differently. The better data structure is:

solution_id    piece
   1             1
   2             2
   3             1
   3             2
. . .

Then you could use joins and more traditional database operations.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes, I was wondering if bit array would be better option for this case. I already have that structure. To store the pieces is good. But to find what pair of solution doesnt have same pieces is very complicated. Maybe you can take a look at this demo. http://rextester.com/GGGUTW70272 – Juan Carlos Oropeza Mar 25 '17 at 01:50