MySQL Query showing duplicate results and taking too much time

Question

Currently I'm working on a social media website where users can post anything just like facebook. Now i've develop an advance search for posts filtering from database. For example, there are 40,000 entries in database and i want to filter rows by advance search. For example if i search Website it shows correct entries but if i search image or video then it duplicates the results.

I only have 40,000 rows but in results it shows 2,51,905 entries. This is my query

SELECT p.id, p.userid, p.description as des, p.likes,
p.comments, p.views, p.post_id, p.created_on, u.name, u.user_name,
u.user_image, p.is_pin
FROM posts p, users u
WHERE p.is_deleted = 0
AND u.is_deleted = 0
and p.userid = u.id
and p.post_text LIKE '%image%' OR p.file_type LIKE '%image%' OR p.post_id LIKE '%image%' OR p.filename LIKE '%image%'

I'm waiting for your response. Thank you.

score 2 · Answer 1 · answered Apr 01 '21 at 15:22

OR conditions in the query should be wrapped within round brackets, which otherwise will return all results irrespective of other conditions in the query.

and (p.post_text LIKE '%image%'
       OR p.file_type LIKE '%image%'
       OR p.post_id LIKE '%image%'
       OR p.filename LIKE '%image%'
)

MySQL Query showing duplicate results and taking too much time

1 Answers1