# Full table scan query on table with 3mm+ rows, a better way?

**URL:** <https://forums.percona.com/t/full-table-scan-query-on-table-with-3mm-rows-a-better-way/1487>\
**Category:** Other MySQL® Questions\
**Created:** [August 26, 2010, 11:40am UTC](https://forums.percona.com/t/full-table-scan-query-on-table-with-3mm-rows-a-better-way/1487 "2010-08-26T11:40:12Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\
**Post date:** [August 26, 2010, 11:40am UTC](https://forums.percona.com/t/full-table-scan-query-on-table-with-3mm-rows-a-better-way/1487/1 "2010-08-26T11:40:12Z")

</div>

Can anyone recommend a better way to do this query?

This query executes about once an hour right now, we may run it more frequently in the future and the amount of rows in the table will continue to grow (currently at around 3mm +).

# Query\_time: 3.202836 Lock\_time: 0.000091 Rows\_sent: 1 Rows\_examined: 3444018 Rows\_affected: 0 Rows\_read: 3444018

select count(\*) from phplist\_usermessage where date\_add(entered,interval 3000 second) \> now() and status = “sent”;

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [August 26, 2010, 11:58am UTC](https://forums.percona.com/t/full-table-scan-query-on-table-with-3mm-rows-a-better-way/1487/2 "2010-08-26T11:58:32Z")

</div>

You will need to use an index to speed this up. Your predicates are

date\_add(entered,interval 3000 second) \> now()  
status = “sent”;

The first one can’t use an index because the ‘entered’ column is inside a function. Place the calculation on the now(), not the column.

The second one is unlikely to be very selective.

See what happens when you get ‘entered’ by itself on one side of the operator.

---

<div class="post-metadata">

**Author:** ![justin.swanhart](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/justin.swanhart/32/16904_2.png) [@justin.swanhart](https://forums.percona.com/u/justin.swanhart)\
**Post date:** [August 26, 2010, 11:58am UTC](https://forums.percona.com/t/full-table-scan-query-on-table-with-3mm-rows-a-better-way/1487/3 "2010-08-26T11:58:43Z")

</div>

Try this:  
Add an index on (entered,sent) and change the query so that you aren’t manipulating the column side of the expression:

select count(\*)  
from phplist\_usermessage  
where entered \> now() - interval 3000 second  
and status = “sent”;

---

<div class="post-metadata">

**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\
**Post date:** [August 26, 2010, 12:04pm UTC](https://forums.percona.com/t/full-table-scan-query-on-table-with-3mm-rows-a-better-way/1487/4 "2010-08-26T12:04:20Z")

</div>

Thanks for the quick response, I’m not exactly sure what you mean in terms of getting the “entered” field by itself? You’re right that indexing won’t work on the calculated field and that indexing on “status” (which exists) is not selective, especially since all records eventually have a “sent” status.

---

<div class="post-metadata">

**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\
**Post date:** [August 26, 2010, 12:10pm UTC](https://forums.percona.com/t/full-table-scan-query-on-table-with-3mm-rows-a-better-way/1487/5 "2010-08-26T12:10:17Z")

</div>

Thank you so much, this worked in 0.065 seconds!

Huge improvement. I’m looking at the modification and it looks OK. Is there any reason to think that:

your updated query:

– entered \> now() - interval 3000 second

is NOT the same as the original:

– date\_add(entered,interval 3000 second) \> now()

I’m looking for double confirmation that the change does not affect anything.