What's the best way to store and search through many product specifications
One way is to design the specifications in a supertable/subtable form. Here is one such design: Fiddle. I'm sure it can be improved on, but the key point is that specification ID values are unique among all specifications, not just within each type of specification. So there are no collisions between Colour and Weight, for example. However, a spec defined as colour cannot be entered into any other spec subtable except Colors (or Colours -- try as I might, I'm afraid I mixed the spelling in at least one location). The super table (Specs) makes it easy to query individual specs.