Query is Very SLow

[jklanka]

I have a table with 3222374 records

when I use “select count(*) from table” it works within a second.

But When I use

select count(*) from table where ts >= ‘2007-01-31 05:21:18’ AND ts <= ‘2008-01-09 05:21:18’

It takes around 20 seconds to get the result.

I have created a index field on ts ( which is DATETIME type field )

Please help on this.

[vicklik]

try to use only 1 row (if possible the primary key) instead of * in count statement:

select count(PrimaryKeyRowName) from table where ts >= ‘2007-01-31 05:21:18’ AND ts <= ‘2008-01-09 05:21:18’

[erkules]

[B]jklanka wrote on Tue, 08 January 2008 22:38[/B]

I have a table with 3222374 records when I use “select count(*) from table” it works within a second. But When I use select count(*) from table where ts >= ‘2007-01-31 05:21:18’ AND ts <= ‘2008-01-09 05:21:18’ It takes around 20 seconds to get the result. I have created a index field on ts ( which is DATETIME type field ) Please help on this.

show us;
EXPLAIN select count(*) from table where ts >= ‘2007-01-31 05:21:18’ AND ts <= ‘2008-01-09 05:21:18’;
and
show table statul like ‘table’\G

have you realy an table named table?

[jklanka]

It takes same time during with single field with primary key. Also table name is tbladd, not table.