the query running too slow

Question

Given is mySQL table named "user_posts" with the following relevant fields:

• user_id
• user_status
• influencer_status

indexed in all three fields

My running slow query is here and also i have created an dbFiddle . Output of Explain is in the dbfiddle:

SELECT 
   P.user_post_id,     
   P.user_id_fk,P.post_type,
   P.who_can_see_post,
   P.post_image_id,P.post_video_id, 
   U.user_name, U.user_fullname,U.influencer_status 
 FROM user_posts P FORCE INDEX (ix_user_posts_post_id_post_type)
   INNER JOIN users U FORCE INDEX (ix_status_istatus)
   ON P.user_id_fk = U.user_id 
 WHERE 
   U.user_status='1' AND 
   U.influencer_status = '1' AND 
   (P.who_can_see_post IN('everyone','influencer','friends')) AND 
   (P.post_type IN('image','video'))
   AND p.user_post_id > 30
 ORDER BY 
    P.user_post_id 
 DESC LIMIT 30

The query takes extremely long, around 6-15 seconds. The database is not very busy otherwise and performs well on other queries.

I am obviously wondering why the query is so slow.

Is there a way to tell exactly what is taking mySQL so long? Or is there any change I need to make to make the query run faster?

Answer 1

The definition of your ix_status_istatus key is preventing it being used to optimise the WHERE clause, as it includes user_id which is not used in the WHERE clause. Redefining the index as

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY ix_status_istatus (user_status, influencer_status);

allows it to be used and should speed up your query, changing the search on users to use index instead of temporary and filesort.

Demo on dbfiddle

Update

Further analysis on dbfiddle suggests that it is also better to remove the FORCE INDEX from the P table as it is not necessary (only the PRIMARY key is required) and changing the JOIN to a STRAIGHT_JOIN i.e. write the JOIN as:

FROM user_posts P 
STRAIGHT_JOIN users U FORCE INDEX (ix_status_istatus)
ON P.user_id_fk = U.user_id

Answer 2

I think You should limit result set of joining part using conditions inside ON statement.

It’s like doing filtering during joining instead of joining then filtering.

I’ve checked query plan which shows me full utilization of indexes.

SELECT 
   P.user_post_id,     
   P.user_id_fk,P.post_type,
   P.who_can_see_post,
   P.post_image_id,
   P.post_video_id, 
   U.user_name, 
   U.user_fullname,
   U.influencer_status 

 FROM user_posts P

 INNER JOIN 
   users U 
       FORCE INDEX (
         users_user_status_index, 
         users_influencer_status_index
       )
   ON 
     U.user_id = P.user_id_fk AND 
     U.user_status='1' AND 
     U.influencer_status='1'

 WHERE 
   P.who_can_see_post IN('everyone','influencer','friends') AND 
   P.post_type IN('image','video') AND 
   P.user_post_id > 30

 ORDER BY 
    P.user_post_id DESC

 LIMIT 30

Indexes that I created:

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY users_user_status_index(user_status),
  ADD KEY users_influencer_status_index(influencer_status);

dbfiddle