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

```

If I run that query it takes a split second but if I switch the comparison operator around it takes eons. Now the first way the count = 0 and the second way the count = 120000, but if I just count the whole table that also takes microseconds.

But there is something something funky going on because if the query ever does finish it runs super quick thereafter. MySQL is caching the query or something right? Well, I don't want to depend on caches to make sure the website doesn't hang. 

This seems nonsensical: if it can count everything greater than a certain date quickly, why should it take any longer to count the opposite? Either way it has to look through the whole table right? And all it needs to return is a number so bandwidth shouldn't be an issue.

Explain on the query:

```
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:

This may be of some interest, I found this info when I run the query:

Handler\_read\_rnd\_next:

* 254436689 (when doing less than)
* 2 (for greater than)

Key\_read\_requests:
314393 vs 33 (33 is the biggest number for all of the stats when using greater than)

Handler\_read\_key:
104303 vs 1

Bypassing the view and running the query directly on the main table eliminates the slowness. So what do I need to do to speed it up? The view is essentially 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

```

---

SOLVED:
Frankie led my in the right direction. The second joined table (the company table) was joined via the full text name of the companies. I only recently decided to add a integer key to that table. The name column was supposed to be indexed but I may have botched that. Anyway I re-organized everything. I converted the foreign key in the main table to match the integer ID of the company table rather than the full company name. I re-indexed those columns in each table, then I updated the view to reflect the new join point. Now it runs instantly in both directions. :) So I guess integer keys were the key. The problem is gone but still, I don't feel like my original question was really solved.

Thanks for your help guys.