tuning the SQL..Reduce the response time

[DaPi]

So, there are a lot of rows . . . all 54'712 have to be read from the table - that could be enough to explain 3 minutes if most are not in buffers (depends on your hardware - I make that 3ms/row).

(check my logic here) I think you can rewrite the query as

SELECT COUNT(*) 
FROM  CART_STATS 
WHERE COMPANY_ID = 8170926
and   ARCHIVE != 'D' 
and   NUM_TRANSACTIONS > 0
and   
(
 (LOTS OF STUFF)
 OR 
 (LOTS OF STUFF)
)

in which case an index on (COMPANY_ID, ARCHIVE, NUM_TRANSACTIONS) could be useful if these extra columns are selective.

So how many rows from:
SELECT COUNT(*)
FROM CART_STATS
WHERE COMPANY_ID = 8170926
and ARCHIVE != 'D'
and NUM_TRANSACTIONS > 0