Query Performance – Confused mysql index priority

Situation

I have the following query that finds 3 candidates whose total number of points is greater than a random number.

SELECT * FROM (
TO SELECT
c.id, c.message, c.timestamp,
vp.category_a, vp.category_b,
(
SI (p.category_a = 1, x, 0) +
SI (p.category_b = 1, x, 0) +
CASE
WHEN DATE_SUB (CURRENT_TIMESTAMP, INTERVAL 5 MINUTE)> c.timestamp THEN x
WHEN DATE_SUB (CURRENT_TIMESTAMP, INTERVAL 10 MINUTE)> c.timestamp THEN X
WHEN DATE_SUB (CURRENT_TIMESTAMP, INTERVAL 20 MINUTE)> c.timestamp THEN x
WHEN DATE_SUB (CURRENT_TIMESTAMP, INTERVAL 40 MINUTE)> c.timestamp THEN x
WHEN DATE_SUB (CURRENT_TIMESTAMP, INTERVAL 80 MINUTE)> c.timestamp THEN x
ELSE 0
END
) Total AS
FROM distributor_candidate c
INNER JOIN (SELECT ID, cid, category_a, category_b of subp profiles) as p
ON DATE_SUB (CURRENT_TIMESTAMP, INTERVAL 360 MINUTE) < c.timestamp                              
         AND c.state = 0                                                                                
         AND c.id NOT IN (a list of cids)        
         AND c.id = p.cid
     INNER JOIN view_profiles vp ON vp.id = p.id                                              
     LEFT JOIN profiles sender_p ON sender_p.cid = 351313                            
     LEFT JOIN ignores ci ON ci.cid = 351313                                         
         AND ci.is_active = 1                                                                           
         AND ci.target_id = c.id                                                                        
 WHERE ci.id is null                                                                                    
 ORDER BY c.timestamp DESC, c.id DESC                                                                   
) AS tmp WHERE tmp.total >= 0.8
LIMIT 3                                                                                                

In the join section, I have the following items

    INNER JOIN (SELECT ID, cid, category_a, category_b of subp profiles) as p
ON DATE_SUB (CURRENT_TIMESTAMP, INTERVAL 360 MINUTE) <c.timestamp
AND c.state = 0
AND c.id NOT IN (cid list)
AND c.id = p.cid

X is a value between 0 and 1, The size of the table candidates is about 24,800 lines, in these lines,

  • 144000 lines have state 0
  • 1200 lines correspond to> 360 minutes of current_timestamp

I have created an index (state, timestamp, id) to improve query performance, and it uses the index, with the result of 802 lines, 50 filtered.

And then I try another index (timestamp, state, id), it results with 1211 lines, 5 filtered

My questions

  1. I do not understand why the result obtained with the second set of indices is less efficient, because there are clearly fewer rows in the pool that meet the requirements.
  2. Looking for a way to further optimize the query, thanks.