MySQL - Query very slow

Question

I have statements like this that are timing out:

SELECT COUNT(*) FROM A WHERE A.value1 IN (
  SELECT A.value1 FROM A WHERE A.value2 = 0
)

Table A has 13,000,000+ rows in it, and because of some reasons, I can't use indexes.

So how to speed it up without use indexes? I'm using MySQL 5.6.

Any help please.

_because of some reasons, I can't use indexes..._ Huh? What reasons should force you to not use indexes? — B001ᛦ, Aug 15 '18 at 12:49
_afraid to slow insert,update_ What is more important for you? Insert? Read? Update? — B001ᛦ, Aug 15 '18 at 15:00
This sql run once a day, but insert run many times. So I prefer to not use indexes — world hello, Aug 16 '18 at 01:15
_So I prefer to not use indexes.._ Sorry but this sounds like a technical suicide — B001ᛦ, Aug 16 '18 at 08:26

score 2 · Answer 1 · answered Aug 15 '18 at 12:52

2

I would first try using exists. This often optimizes better:

SELECT COUNT(*)
FROM A 
WHERE EXISTS (SELECT 1
              FROM A A2
              WHERE A2.value2 = 0 AND A2.value1 = A.value1
             );

The optimal index for this is A(value2, value1).

You could also use two levels of aggregation:

select sum(cnt)
from (select value1, count(*) as cnt
      from a
      group by value1
      having sum(value2 = 0) > 0
     ) a;

answered Aug 15 '18 at 12:52

Gordon Linoff

1 Answers1