•  

Announcement

Announcement Module
Collapse
No announcement yet.

Query is Very SLow

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query is Very SLow

    #1
    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.
    Tags: None
  • #2
    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'

    Comment

    • #3
      jklanka wrote on Tue, 08 January 2008 22:38

      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?

      Comment

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

        Comment

        Working...
        X