Help with tricky mysql query

gyaaniguy — Fri, 04 Aug 2023 10:20:54 +0000

Did not work. ~~SOLVED: maybe. will update later.~~

DB structure:

section
id 

posts:
id | updated_at | section_id

form_element: 
id | type 

post_form_element_values :
post_id | form_element_id | value 

- Each section can have multiple posts. 
- posts and form_element tables have a many to many relationship.
- form_element: type col can be either 0 OR 1

Task: Get all posts of a particular section that satisfy the criteria
type = 2 AND f.value < '2023-08-04 02:33:11' OR type=0
Note: all posts of a section will have the same type value (0 OR 1)

I wrote this :

SELECT p.id 
FROM posts p
WHERE  p.section_id = 4 and p.id NOT IN (SELECT f.post_id 
                   FROM post_form_element_values f
                            LEFT JOIN form_element v ON v.id = f.form_element_id
                    WHERE (v.type = 2 AND f.value > '2023-08-04'))
ORDER BY p.id
LIMIT 10

Works perfectly - 50ms.
But changing "ORDER BY p.id" to "ORDER BY p.updated_at" slows it - 3 seconds
But removing (NOT IN) and keeping "ORDER BY p.updated_at" is still fast.
On its own the subquery is fast - 30ms

How can I write this differently ? Stuck for days. I tried joins and not exists. this is killing me...

Explanation of various experiments:

ORDER BY updated_at AND 'not in' condition' (slow)

1,PRIMARY,s,range,section_id_idx,section_id_idx,,9,114764,Using index condition; Using where; Using filesort
2,DEPENDENT SUBQUERY,s3,ref,entry_id_idx,"answer_index_idx,entry_id_idx,form_element_id_idx",func,8,3,Using where
2,DEPENDENT SUBQUERY,s4,eq_ref,PRIMARY,"PRIMARY,tenant_id_idx,form_id_idx,widget_id_idx,privacy_idx,form_element_category_id_form_element_id",.s3.form_element_id,8,1,Using where

ORDER BY id ** AND 'not in' Condition (fast)
1,PRIMARY,s,ref,section_id_idx,section_id_idx,9,const,114764,Using where; Using index**
2,DEPENDENT SUBQUERY,s3,ref,"answer_index_idx,entry_id_idx,form_element_id_idx",entry_id_idx,8,func,3,Using where
2,DEPENDENT SUBQUERY,s4,eq_ref,"PRIMARY,tenant_id_idx,form_id_idx,widget_id_idx,privacy_idx,form_element_category_id_form_element_id",PRIMARY,8,labspadang.s3.form_element_id,1,Using where
ORDER BY p.updated_at AND 'NOT IN' is removed (fast)
1,SIMPLE,s,range,section_id_idx,section_id_idx,9,,114764,Using index condition; Using filesort

help please

DEV Community: gyaaniguy

Help with tricky mysql query