It seems to me that sometimes using hash tables is not required and thus no need to make another expensive JOINs. Especially, when the number of attributes can be foreseen, you can simply set some bit to on.
\nImagine, we have a product which has two attributes: new, promotion. Usually, we create 2 tables, `Product` and `Product_To_Attribute` (as hash table), then while searching products by attributes we prepare query as below:<\/p>\n
SELECT *\r\nFROM `product` `Prod` INNER JOIN Product_To_Attribute `Prta`\r\nWHERE `Prta`.`Attribute_Id` in (1,2);<\/pre>\nEverything is fine untill your data are small. Therefore, I prefer to remove table `Product_To_Attribute` replacing it by “bit index” in `product` table. I name it “bit representation”.
\nHow it works? Suppose, that we have only two attributes new (represented by first bit<\/span>), promotion (represented by second bit<\/span>). As I said, if product has an attribute its position is ON, therefore the following states are possible:
\n00<\/span> (no attributes);
\n01<\/span><\/span> (is new);
\n1<\/span>0<\/span> (is promoted);
\n1<\/span>1<\/span><\/span> (is new & promoted);<\/p>\nHow to search through attributes<\/h3>\n
As you probably know, a\/m bit states are numbers, 00 is 0, 01 is 1, 10 is 2, etc. So if we would like to remove JOIN from our first query simply you must store attributes as integer. Our product is new and promoted, so in bits it is 1<\/span>1<\/span><\/span>, as integer it is 3. Now, we add a column `Attribute_in_bits` and update this column at our product to 3 (Update `product` SET `Attribute_bit_idx`= 3 WHERE Id = ‘OurProduct’; And now simple query can be used:<\/p>\n-- This collects all products with attributes new or promoted\r\nSELECT * FROM `product` WHERE `Attribute_bit_idx` in (1, 2 ,3);\r\n-- This collects all promoted products.\r\nSELECT * FROM `product` WHERE `Attribute_bit_idx` in (2 ,3);<\/pre>\nObviuosly, you can use native MySQL functions to find adequate rows, however “EXPLAIN” (in case of MySQL) will explain you the costs.<\/p>\n
-- This collects all products with attributes new or promoted\r\nSELECT * FROM `product` WHERE (`Attribute_bit_idx` & 1|2) ;\r\n-- This collects all promoted products.\r\nSELECT * FROM `product` WHERE (`Attribute_bit_idx` & 2) ;<\/pre>\nHow to prepare data<\/h4>\n
All a\/m operations are rather simple, more difficult is to prepare data. Here is example of my own way (in PHP).<\/p>\n
Update index<\/h5>\n
‘OurProduct’ obtains new attribute – ‘new’; integer representation of this attribute is 1 (first bit ON).<\/p>\n
\r\nUPDATE `product` SET `Attribute_bit_idx` = `Attribute_bit_idx` + 1\r\n WHERE Id = 'OurProduct'\r\n AND \/* check whether attribute exists *\/ (`Attribute_bit_idx` & 1)=0; \r\n<\/pre>\n‘OurProduct’ obtains new attribute – ‘bestseller’; integer representation of this attribute is 8 (fourth bit ON).<\/p>\n
\r\nUPDATE `product` SET `Attribute_bit_idx` = `Attribute_bit_idx` + 8\r\n WHERE Id = 'OurProduct'\r\n AND \/* check whether attribute exists *\/ (`Attribute_bit_idx` & 8)=0; \r\n<\/pre>\nUsing index<\/h5>\n
For this purpose I wrote class Shop_Bitwise which is kind of helper for bit operations. Additionaly, at the bottom you will find class Shop_Product which contains attributes as consts. I do not translate integers into bits cause I assume that attribute number conforms to bit position. (int 1 is 1, int 2 is 2, int 3 is 4, int 4 is 8, etc.).
\nSo, I want to update product ‘OurProduct’ to set new attribute. Identity number of new attribute is 1. I must know the bit which represents this number:<\/p>\n
\r\n\nHow to find rows by chosen attributes?<\/h4>\n\r\nNumbers();\r\n$Query = \"SELECT Id, Attribute_bit_idx FROM `product` \r\n WHERE `Attribute_bit_idx` IN (\".implode(\",\", $SearchedIntegers ).\");\";\r\n<\/pre>\nHow to translate attributes to integers?<\/h4>\n
Having instance of class Shop_Bitwise we can use method ExtractAttributesFromInt() which returns array containing identifier of attributes.<\/p>\n
\r\nExtractAttributesFromInt($row['Attribute_bit_idx']);\r\n<\/pre>\nPHP Classes:<\/p>\n
\r\n_setAttributes($attr);\r\n\t\t$this->_setSearched($searched);\r\n\t}\r\n\t\r\n\tpublic function Numbers()\r\n\t{\r\n\t\tif ($this->_numbers == null):\r\n\t\t\t$this->_generate();\r\n\t\tendif;\r\n\t\treturn $this->_numbers;\r\n\t}\r\n\r\n\tpublic function ExtractAttributesFromInt($i)\r\n\t{\r\n\t\t$attributes = array();\r\n\t\tforeach ($this->_attr as $attr):\r\n\t\t\tif ($i & $attr):\r\n\t\t\t\t$attributes[] = $attr;\r\n\t\t\tendif;\r\n\t\tendforeach;\r\n\t\treturn $attributes;\r\n\t}\r\n\t\r\n\tpublic static function GetAttributeBitFromInt($i)\r\n\t{\r\n\t\treturn 0 | $i;\r\n\t}\r\n\t\r\n\tpublic static function GetAttributeIntFromBit($i)\r\n\t{ \r\n\t\treturn pow(2,$x-1);\r\n\t}\t\r\n\r\n\tprivate function _setSearched(array $a)\r\n\t{\r\n\t\t$this->_searched = $a;\r\n\t}\r\n\t\r\n\tprivate function _searched()\r\n\t{\r\n\t\treturn $this->_searched;\r\n\t}\r\n\r\n\tprivate function _setAttributes(array $a)\r\n\t{\r\n\t\t$this->_attr = $a;\r\n\t}\r\n\t\r\n\tprivate function _attributes()\r\n\t{\r\n\t\treturn $this->_attr;\r\n\t}\r\n\t\t\r\n\tprivate function _generate()\r\n\t{\r\n\t\t$searched = $this->_searched();\r\n\t\t$dostepneAtrybuty = $this->_attributes();\r\n\t\tsort($dostepneAtrybuty);\r\n\t\tsort($searched);\r\n\t\t$highestSearch = end($searched);\r\n\t\t\r\n\t\t$iterator \t\t= 0;\r\n\t\t$subIterator \t= 0 ;\r\n\t\t$result \t\t= array();\r\n\t\t\r\n\t\tforeach ($dostepneAtrybuty as $attr):\r\n\t\t\tif ($attr > $highestSearch):\r\n\t\t\t\tbreak;\r\n\t\t\tendif;\r\n\t\t\t$attrPassed = array();\r\n\t\t\t$attrPassed[] = $attr;\r\n\t\t\r\n\t\t\t$subIterator = 0 ;\r\n\t\t\tforeach ($dostepneAtrybuty as $attr2):\r\n\t\t\t\t\/\/ zabezpieczenie przed powtarzalnoscia\r\n\t\t\t\tif ( $attr2 == $attr):\r\n\t\t\t\t\tcontinue;\r\n\t\t\t\tendif;\t\t\t\t\r\n\t\t\t\t\/\/ zabezpieczenie przed powtarzalnoscia 2\r\n\t\t\t\tif ($subIterator < $iterator):\r\n\t\t\t\t\t$subIterator++;\r\n\t\t\t\t\tcontinue;\r\n\t\t\t\telse :\r\n\t\t\t\t\t$subIterator++;\r\n\t\t\t\tendif;\r\n\t\t\t\t\r\n\t\t\t\t$attrPassed [] \t= $attr2;\r\n\t\t\tendforeach;\r\n\t\t\t\r\n\t\t\t$variations = $attrPassed;\r\n\t\t\t$result [] = array($variations[0]);\r\n\t\t\t$variations[0] = false;\r\n\t\t\t$variations = array_filter($variations);\r\n\t\t\tsort($variations);\r\n\t\t\r\n\t\t\t$digitQty = count($variations); \/\/ number of digits\r\n\t\t\tfor ($i=0; $i < $digitQty ; $i++ ):\r\n\t\t\t\t$elements = $digitQty-$i;\r\n\t\t\t\t$hop = 0;\r\n\t\t\t\t$first = current ($variations);\r\n\t\t\t\twhile(true):\r\n\t\t\t\t\t$newAr = array();\r\n\t\t\t\t\t$cnt = $hop;\r\n\t\t\t\t\tfor ($i2=0; $i2 < $elements;$i2++):\r\n\t\t\t\t\t\t$cnt = $hop + $i2;\r\n\t\t\t\t\t\tif ($cnt >=\t $digitQty){\r\n\t\t\t\t\t\t\t$cnt = abs ( ($digitQty - $cnt) );\r\n\t\t\t\t\t\t}\r\n\t\t\t\t\t\t$newAr [] = $variations[$cnt];\r\n\t\t\t\t\tendfor;\r\n\t\t\t\t\tif ( current($newAr) == $first && $hop > 0 ):\r\n\t\t\t\t\t\tbreak;\r\n\t\t\t\t\tendif;\r\n\t\t\t\t\t$result [] = array_merge_recursive( array($attrPassed[0]), $newAr);\r\n\t\t\t\t\t$hop++;\r\n\t\t\t\tendwhile;\t\r\n\t\t\tendfor;\t\r\n\t\t\t$iterator++;\r\n\t\tendforeach;\r\n\t\t\r\n\t\t$unique = array();\r\n\t\tforeach ( $result as $n ):\r\n\t\t\tforeach ($searched as $searchedValue):\r\n\t\t\t\tif (in_array($searchedValue, $n)):\r\n\t\t\t\t\t$unique [] = array_sum( $n );\r\n\t\t\t\t\tcontinue;\r\n\t\t\t\tendif;\r\n\t\t\tendforeach;\r\n\t\tendforeach;\t\t\r\n\t\tsort($unique);\r\n\t\t$this->_numbers = array_unique($unique);\r\n\t}\r\n}<\/pre>\n\r\n\n","protected":false},"excerpt":{"rendered":"It seems to me that sometimes using hash tables is not required and thus no need to make another expensive JOINs. Especially, when the number of attributes can be foreseen, you can simply set some bit to on. Imagine, we have a product which has two attributes: new, promotion. Usually, we create 2 tables, `Product` […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[9,10],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/93"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=93"}],"version-history":[{"count":58,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/93\/revisions"}],"predecessor-version":[{"id":186,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/93\/revisions\/186"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=93"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=93"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=93"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}