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 whereORDER 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
Top comments (1)
Still need help please !