CODEWITHSUNDEEP
sphinx
Doni
Doni

Reputation: 27

How create the correct index for the sphinx

I have tables:

product 
| id | shop_id | name |
| 1  |  1      | p1   |
| 2  |  1      | p2   |
| 3  |  2      | p3   |
etc

shop
| id | name |
| 1  | s1   |
| 2  | s2   |
| 3  | s3   |
| 4  | s4   |
etc

country
| id | name   |
| 1  | russia |
| 2  | usa    |
etc

shop_country
|id | shop_id | country_id |
| 1 | 1       |   1        |
| 2 | 1       |   2        |
| 3 | 2       |   1        |

I need to get the goods that are available in a set country.

that's a work request from mysql:

SELECT *
FROM product AS p
LEFT JOIN shop_country sc ON (p.shop_id = sc.shop_id)
WHERE product.status = 1 mc.country_id = 3

I created an index:

SELECT product.id as id, product.shop_id \
FROM product \
LEFT JOIN shop_country sc ON (product.shop_id = sc.shop_id) \                                
GROUP BY product.id

but it produces the wrong result

What do I need to get this in the Sphinx?

Upvotes: 0

Views: 35

Answers (1)

Moosh
Moosh

Reputation: 396

GROUP BY product.id

means you have many country for same product

SELECT product.id as id, product.shop_id \

No group function

So replace by 

SELECT product.id as id, group_concat(product.shop_id)  \

and try to set shop_id as sql_attr_multi

Upvotes: 1

Related Questions