CODEWITHSUNDEEP
mysqlsql
Dmitriy Zhura
Dmitriy Zhura

Reputation: 152

Mysql query to select products by filters

I'm trying to create a SELECT query to get products by some filters. You can see DB structure on the screen below. DB structure definitions - products types, features - products features (e.g. price, size, color), significations - value on some feature, objects - avalible products on site, trans_obj2sign - auxiliary table to make many to many relations.

I'm creating query using php considering selected filters:

SELECT o.id, o.name, o.url, GROUP_CONCAT(f.name separator '--|||--') as feature_name,        GROUP_CONCAT(f.title separator '--|||--') as feature_title, GROUP_CONCAT(s.value separator '--|||--') as signification_value
FROM objects as o
JOIN trans_obj2sign o2s ON o.id=o2s.object_id
JOIN features f ON o2s.feature_id=f.id
JOIN significations s ON o2s.signification_id=s.id
WHERE o.id IN (
    SELECT o2s.object_id
    FROM trans_obj2sign o2s
    JOIN significations s ON ( o2s.signification_id = s.id ) 
    WHERE (
    (s.id IN ("2",  "43",  "48",  "55") AND s.feature =13)
    OR (s.id IN ("14",  "23",  "49",  "52") AND s.feature =14)
    OR (s.value BETWEEN 0 AND 500 AND s.feature =17)
    )
    GROUP BY o2s.object_id
    HAVING COUNT( o2s.object_id ) =3
)
GROUP BY o.id
LIMIT 0, 50

Inner query returns ids of objects which significations approach to all filters. Main query returns products attributes that are needed.

The questions are:

  1. Can I rewrite this query to perform faster? (this one is pretty heavy)
  2. What indexes should I add?
  3. How can I add ORDER BY some signification value. Something like WHERE s.feature=17 ORDER BY s.value (pretend that s.feature=17 is product price)

Upvotes: 0

Views: 1796

Answers (1)

StanislavL
StanislavL

Reputation: 57381

Get rid of the IN+subquery. Just move it to the FROM section to be executed just once.

Like this

SELECT o.id, o.name, o.url, GROUP_CONCAT(f.name separator '--|||--') as feature_name,        GROUP_CONCAT(f.title separator '--|||--') as feature_title, GROUP_CONCAT(s.value separator '--|||--') as signification_value
FROM objects as o
JOIN trans_obj2sign o2s ON o.id=o2s.object_id
JOIN features f ON o2s.feature_id=f.id
JOIN significations s ON o2s.signification_id=s.id
JOIN (
    SELECT o2s.object_id
    FROM trans_obj2sign o2s
    JOIN significations s ON ( o2s.signification_id = s.id ) 
    WHERE (
    (s.id IN ("2",  "43",  "48",  "55") AND s.feature =13)
    OR (s.id IN ("14",  "23",  "49",  "52") AND s.feature =14)
    OR (s.value BETWEEN 0 AND 500 AND s.feature =17)
    )
    GROUP BY o2s.object_id
    HAVING COUNT( o2s.object_id ) =3
) filtered on filtered.id=o.id
GROUP BY o.id
LIMIT 0, 50

Add indexes for fields which are used in WHERE.

To add ORDER BY place the query in a FROM section (as subquery) and use ORDER BY

Upvotes: 1

Related Questions