I would like to know what is the best way to query a recordset with the following structure:
1 table containing about 100.000 products
1 table containing product_groups (about 2.000 records).
1 table containing specs about each product in the product table. On average each product has about 35 specs.
1 table containing specs (about 200 records).
Example table structure
Field: product_id [unique, indexed]
Field: product_group_id [indexed]
Field: product_group_id [unique, indexed]
Field: spec_id [unique, indexed]
Now what is the fastet way to find records in a specific group that has one or more specific specs.
If for some reason another table structure is required to optimize querying the dataset please include this in you response. I have no problem altering the structure if this improves speed.
Below an example how i currently extract data. This example gets all products in product_group_id 100 with the specs 100 and 101.
SELECT product_id from products
Inner Join spec_product as spec_product1 ON product.product_id = spec_product1.product_id AND spec_product1.specs_id = 100
Inner Join spec_product as spec_product2 ON product.product_id = spec_product2.product_id AND spec_product2.specs_id = 101
Where product_group_id = 100
For some reason when there are more the 7 inner joins the time it takes to get the products increases rapidly.
Well hope anyone can help.