Nia Nia - 3 months ago 9
SQL Question

Select statement takes too long to finish

I have the following query,

SELECT 1
FROM orderdetail -- table orderdetail has 1420703 rows
WHERE orderdetailstatusid != 3 -- not cancelled
AND
navdate = '20161212';


The problem is that it takes more than 130 seconds to execute since its scanning a huge table with ~1420703 rows. I was wondering if there is a way to make it a bit faster.

I tried to do
limit 1
at the end but that also did not help.

Can some one please give some suggestion, how to improve it?

Thank you in advance.

Answer

And to add my contribution:

For best performance:

Firstly you should have your 2 columns indexed as proposed by macco

Secondly, != and <> operators are more intensive for the engine than = . When you can, it's always better to list all possible values rather than say different than value. It looks like it is the case here. So you can rewrite your query like this:

SELECT  1
FROM  orderdetail               -- table orderdetail has 1420703 rows
WHERE orderdetailstatusid IN (1,2,4,5)  -- everything but cancelled
  AND navdate = '20161212';

Supposing that you you can only have orderdetailstatusid values like (1,2,3,4,5), change accordingly.