0

I have a mysql 8 query like below. the ch_site_user table is indexed on user_id, site_id, status.
Explain plan shows the correct index is used. But usersite.site_id IN (2315, 2316, 2324, 2323, 2320, 2322, 2326, 2319, 2318, 2317, 2325, 2345, 2346, 2330, 2347, 2348, 2350) came in the Filter portion. Please correct the query so that the output can come without filter

Query:

EXPLAIN ANALYZE  SELECT `usersite`.`id` AS `usersite.id` 
    FROM `cometchat_users` AS `user` 
    LEFT OUTER JOIN `ch_site_user` AS `usersite`
    ON  `user`.`userid` = `usersite`.`user_id`
    WHERE
    `user`.`tenantid` = 784 and 
    `usersite`.`site_id` IN (2315, 2316, 2324, 2323, 2320, 2322, 2326, 2319, 2318, 2317, 2325, 2345, 2346, 2330, 2347, 2348, 2350)

EXPLAIN output:

-> Limit: 200 row(s)  (cost=348966 rows=200) (actual time=0.0507..0.222 rows=200 loops=1)
    -> Nested loop inner join  (cost=348966 rows=237993) (actual time=0.0499..0.211 rows=200 loops=1)
        -> Covering index lookup on user using X_cu_tenantid (tenantid=784)  (cost=75398 rows=752098) (actual time=0.0287..0.0344 rows=18 loops=1)
        -> Filter: (usersite.site_id in (2315,2316,2324,2323,2320,2322,2326,2319,2318,2317,2325,2345,2346,2330,2347,2348,2350))  (cost=0.25 rows=0.316) (actual time=0.00373..0.00903 rows=11.1 loops=18)
            -> Covering index lookup on usersite using U_csu_user_site_status (user_id=`user`.userid)  (cost=0.25 rows=1.14) (actual time=0.0034..0.0078 rows=11.8 loops=18)
Improve this question
1
  • the ch_site_user table is indexed on user_id, site_id, status Show this as complete CREATE TABLE script. came in the Filter portion Too many values (above ~10). correct the query so that the output can come without filter 1) Create composite index usersite (user_id, site_id). 2) Force its usage if needed. 3) Maybe convert the list to rowset. Commented Oct 13, 2023 at 7:44

2 Answers 2

Reset to default
1

"Danger, Will Robinson!"
Make sure this join is behaving the way you think it is.

Putting WHERE conditions on the "right" table in a LET OUTER JOIN can collapse the join's 'OUTER'ness, reducing it an INNER join.

For such cases, put the conditions on the "right" table into the join's "on" clause, instead of the WHERE clause.

SELECT usersite.id AS usersite.id 
FROM cometchat_users AS user 
LEFT OUTER JOIN ch_site_user AS usersite
   ON user.userid = usersite.user_id
  AND usersite.site_id IN (2315, 2316, 2324, 2323, 2320, 2322, 2326, 2319, 2318, 2317, 2325, 2345, 2346, 2330, 2347, 2348, 2350) 
WHERE user.tenantid = 784 
...

Regards, Phill W.

Improve this answer
1
  • The point is -- For LEFT, placement of conditions (ON vs WHERE) matters. For INNER JOIN (aka JOIN), the query results will be the same either way. Commented Oct 13, 2023 at 18:51
0

Since the LEFT JOIN is really JOIN, the Optimizer gets to choose which table to start with. The following "composite" indexes will let it pick either direction and make a rational decision if which is best.

usersite:  INDEX(site_id, user_id, id)
usersite:  INDEX(user_id, site_id, id)
user:  INDEX(tenantid, userid)

Those are also "covering", which gives an extra boost in performance.

In general, IN optimizes somewhere between = and a "range".

When asking performance questions, be sure to provide SHOW CREATE TABLE.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.