Why is this query slow?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Why is this query slow?

    I have a table with this structure that has 1.3 million rows:

    CREATE TABLE `KeywordST` ( `ID` int(10) unsigned NOT NULL, `BNDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `BNCount` int(11) NOT NULL default '0', PRIMARY KEY (`ID`), KEY `BNDate` (`BNDate`,`BNCount`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

    I did an explain on a query I use to get data:

    explain select count(ID) from KeywordST where BNDate < subtime( now( ) , '12:00:00.0' ) limit 20\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: KeywordST type: rangepossible_keys: BNDate key: BNDate key_len: 4 ref: NULL rows: 743702 Extra: Using where1 row in set (0.01 sec)

    It looks ok to me but I'm sure I'm missing something because this query takes 2.82 seconds to execute!

    Any ideas?

    Edit: I really only need to know if there will be 20 matches or not...hmmm...ref means it's doing a full table scan doesn't it?

  • #2
    Duh...Answered my own question )

    All I needed to do was mysql_num_rows instead of trying to count(ID) )