CODEWITHSUNDEEP
mysqldatetimeindexingdatabase-performance
Aamir
Aamir

Reputation: 758

why greater than in where clause causing full table scan?

I have a table named x with INDEX on datetime column-> eventdate .

when i am using 

"where eventdate >= '2013-09-01 00:00:00'"

this one causing full table scan. what should i do to avoid full table scan. Please help.

Upvotes: 1

Views: 1026

Answers (1)

Jason Heo
Jason Heo

Reputation: 10246

So you have INDEX(eventdate) but where eventdate >= '2013-09-01 00:00:00' does full scan? then I guess most of eventdate is greater than '2013-09-01 00:00:00'

would you give us following query outout?

SELELCT COUNT(*) FROM x;
SELELCT COUNT(*) FROM x WHERE  eventdate >= '2013-09-01 00:00:00;
EXPLAIN SELELCT COUNT(*) FROM x WHERE  eventdate >= '2013-09-01 00:00:00;

MySQL says as follows:

http://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html

You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.

UPDATE

OP has commented like below.

Q1 : SELELCT COUNT(*) FROM x; 

Q2 : SELECT COUNT(c.id)
     FROM X c WHERE c.eventDate >= '2013-09-30 09:17:35'
     GROUP BY c.msisdn
     ORDER BY SUM(abc) DESC;

Both "Q1" and "Q2" produce 20,000,042 rows. OP is asking "now please tell me why its retrieving all data".

as I mentioned earlier, all eventData in table x is greater than "2013-09-30 09:17:35". So whole data is retrieved. If you want top 10 records, just add "LIMIT"

SELECT COUNT(c.id)
FROM X c WHERE c.eventDate >= '2013-09-30 09:17:35'
GROUP BY c.msisdn
ORDER BY SUM(abc) DESC
LIMIT 10;

you query is doing GROUP BY and ORDER BY over 20M+ rows, it takes long long time (how long takes it?) when more conditions added to query, it is getting faster. But I have no idea which condition is proper (I don't know about your data, schema, what do you want)

Upvotes: 2

Related Questions