Query performance issues after using IS NULL condition in where clause

Asked Jan 12 '15 at 07:40

Active Jan 12 '15 at 08:15

Viewed 157 times

I am using MySQL. I have a query as shown below :-

Select col1,col2,col3...,coln from table a 
left join table b ON a.id = b.id
left join table c on a.id = c.id
where a.id NOT IN (1,2,3)

Above query worked perfectly fine returning results in 5 seconds. It did not return any results for which a.id is NULL I also wanted to include rows who have a.id is null. So I updated my query to include that

Select col1,col2,col3...,coln from table a 
left join table b ON a.id = b.id
left join table c on a.id = c.id
where a.id NOT IN (1,2,3) or a.id is NULL

But I guess, I am doing something wrong as query is running for quite a long time and then I am breaking the query.

Could any body please let me know why is there sudden decrease in performance?

I guess it is because of adding a.id is NULL, If that is the case, how can I include that in my condition to not affect performance.

Thanks

UPDATE

Not sure if relevant, but, in MYSQL output window, when I run this query, DUration/Fetch shows as .050 sec/ ?

Meaning Duration is just .05 sec but fetch is what is taking the long time.

edited Jan 12 '15 at 08:15

asked Jan 12 '15 at 07:40

Neil

1,715
6
30
45

How many records we talking about here? with or condition and without or condition? – Suraj Singh Jan 12 '15 at 07:44
Use `EXPLAIN` for query tuning. – Hamlet Hakobyan Jan 12 '15 at 07:49
I do not have permissions to use "EXPLAIN" – Neil Jan 12 '15 at 07:51
There are around 200,000 records – Neil Jan 12 '15 at 07:52
Is `ID` indexed? What if you write it without `NOT IN`? `WHERE a.ID IS NULL OR a.ID<>1 OR a.ID<>2 OR a.ID<>3`. – Tim Schmelter Jan 12 '15 at 07:57
You need to index id if not. Also you should not use IN as it will affect performcance. You can change like WHERE a.ID IS NULL OR a.ID!=1 AND a.ID!=2 AND a.ID!=3 – Sridhar DD Jan 12 '15 at 08:04
a.id is defined as Primary key. So should not be a problem – Neil Jan 12 '15 at 08:09
1

How can the ID be NULL? It's the primary key! – Thorsten Kettner Jan 12 '15 at 09:28

Query performance issues after using IS NULL condition in where clause

0 Answers0