Hi Ravi, all!
Ravi Malghan wrote:
Ok. I found something. But wondering if this is most
efficient
Events for yesterday:
select count(*) from EVENT_DATA where
FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() -
INTERVAL 1 DAY);
Events for last week
select count(*) from EVENT_DATA where
FROM_UNIXTIME(utime,'%U') =
(DATE_FORMAT(CURDATE(),'%v')-1);
TIA
Ravi
--- Ravi Malghan <[EMAIL PROTECTED]> wrote:
Hi: I have a date/time field (utime) which has unix
time in epoch time. I want to select events from
yesterday and another statement for all events from
previous week.
No, that is not the most efficient way.
If you have any sizable amount of data, you need an index to allow your
"where condition" to be evaluated without accessing all records (also
called "table scan").
For the efficient use of that index, you should ensure that the "where
condition" is of the form
column_value comparison_operator expression
It does not matter whether "expression" is complicated, it needs to be
computed only once, but "column_value" should just be the column name
and not a function / expression using it.
So what you need is
select count(*) from EVENT_DATA
where utime > (seconds of your period start)
Sorry, I lack the time to scan the manual for the correct expression to
calculate that start value.
HTH,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
