MySQL - Why is COUNT with “greater than” fast, but “less” takes forever?

Question

SELECT count(*) c FROM full_view WHERE verified > ( DATE (NOW()) - INTERVAL 30 DAY)

If I run this query, it takes a second fraction, but if I switch the comparison operator around it, it will take several seconds. Now the first way is count = 0 and the second way is count = 120000, but if I just count the whole table, which also takes microseconds.

But there is something funky, because if the request ever ends, it quickly starts faster. Does MySQL cache a query or something like that? Well, I don’t want to depend on caches to make sure that the site is not hanging.

This seems pointless: if he can quickly calculate everything that will be more than a certain date, why do you need to count more to count the other way around? In any case, should he look at the whole table directly? And all he needs to return is the number, so bandwidth should not be a problem.

Explain by request:

1, 'SIMPLE', 'b', 'range', 'updated,verified_index', 'updated', '3', '', 28, 'Using where'`    
1, 'SIMPLE', 'l', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'xyz_main.b.loc_id', 1, 'Using index'
1, 'SIMPLE', 'f', 'ALL', '', '', '', '', 2214, ''

---

EDIT:

It may be interesting, I found this information when running the request:

Handler_read_rnd_next:

• 254436689 (when executed less)
• 2 (more)

Key_read_requests: 314393 vs 33 (33 is the highest number for all characteristics when using a larger one)

Handler_read_key: 104303 vs 1

Bypassing the view and running the query directly in the main table, slowness is eliminated. So what do I need to do to speed it up? The view looks something like this:

SELECT x, y, z, verified FROM table1 LEFT JOIN table2 on tab2_ID = table2.ID LEFT JOIN table3 on tab3_ID = table3.ID

---

: . ( ) . . , , , . . , , . , , . .:) , , . , , , .

.

Answer 1

, .

EXPLAIN SELECT count(*) c 
FROM full_view 
WHERE verified > ( DATE (NOW()) - INTERVAL 30 DAY)

EXPLAIN - !;)

---

1:
, :

1, 'SIMPLE', 'f', 'ALL', '', '', '', '', 2214, ''

ALL , FULL .

EXPLAIN .

, ...

---

2:
EXPLAIN. , .

---

3:
.

1, 'SIMPLE', 'b', 'range', 'updated,verified_index', 'updated', '3', '', 28, 'Using where'`    
1 - id
SIMPLE - simple select, not using sub-queries
b - table name
range - only rows that are in a given range are retrieved, using an index
updated,verified_index - are both possible keys
updated - was the key eventually used
3 - key lenght
'' - this is the ref column and would show which columns or constants are compared to the index name in the key column to select rows from the table.
28 - number of rows mysql believes it must examine to execute the query
Using where - self explanatory

Answer 2

, Date(Now()) - , . verified, Date(Now()), , ( ", " ).

, "", , , datetime

, - .

Answer 3

, , , > 30 . , .

Answer 4

If you have an index on verifiedin the table, stricter COUNT(> one) will be faster. COUNT (*) without a WHERE clause can return quickly, because the score can be gleaned from the table / index statistics.