select DISTINCT id_request,username_request from darkhast_follower where darkhast_follower.id_request != '9762952594' AND darkhast_follower.id_request != 'null' AND darkhast_follower.status !=1 AND NOT exists ( select * from log_follow where log_follow.other_id = darkhast_follower.id_request AND log_follow.id= '9762952594') LIMIT 5
Which column should be indexed? Or what is the best query to replace this?
No index would be helpful in terms of
If you have the table of N rows and
key=val yields the
k rows of result, then
N-k rows. Depending on the key’s selectivity
N-k tends to
N. So search on inequality is equivalent to the full table scan for all cases and can’t be improved by index.
Do not say
IS NOT NULL
That is, unless you are actually storing the 4 characters “null” in a
log_follow: INDEX(other_id, id) -- for the subquery darkhast_follower: no index is useful for this query
The query, as you have it, will scan
darkhast_follower until it finds 5 rows matching the all the
WHERE clauses. In doing so, it will check
log_follow — This task will be a table scan (slow) if you don’t have a suitable index. Else it will be fast.