CODEWITHSUNDEEP
mysqlsql
Sailormoon
Sailormoon

Reputation: 267

mysql tunning timestamp performance?

Hey guys I have a quick question regarding sql performance. I have a really really large table and it takes forever to run the query below, note that there is a column with timestamp

select name,emails,
   count(*) as cnt
from table
where date(timestamp) between '2016-01-20' and '2016-02-3'
and name is not null
group by 1,2;

So my friend suggested to use this query below:

select name,emails,
   count(*) as cnt
from table
where timestamp between date_sub(curdate(), interval 14 day)
and date_add(curdate(), interval 1 day) 
and name is not null
group by 1,2;

And this takes much less time to run. Why? What's the difference between those two time function? And is there another way to run this even faster? Like index?Can someone explain to me how mysql runs? Thanks a lot!

Upvotes: 0

Views: 885

Answers (1)

Zafar Malik
Zafar Malik

Reputation: 6854

just add index on timestamp field and use query as per below-

select name,emails,
   count(*) as cnt
from table
where `timestamp` between '2016-01-20 00:00:00' and '2016-02-03 23:59:59'
and name is not null
group by 1,2;

Why? What's the difference between those two time function

In first query you are getting dates from your own column but with date() function due to this reason mysql is not using index and doing table scan while 2nd suggested table you have removed date(timestamp) function so now mysql will check values from index instead of table scan so it is fast.

Same mysql will use index in my table also.

Upvotes: 2

Related Questions