MySQL Performance: MySQL => Why is this query slow?

Members

Help

Home

Home » Performance » MySQL » Why is this query slow?

Show: Today's Messages :: Show Polls :: Message Navigator

Why is this query slow? [message #1355]

Fri, 01 June 2007 18:59

GeoffreyF67
Messages: 21
Registered: May 2007

Junior Member

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: range
possible_keys: BNDate
          key: BNDate
      key_len: 4
          ref: NULL
         rows: 743702
        Extra: Using where
1 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?

[Updated on: Fri, 01 June 2007 19:05]

G-Man

Report message to a moderator