how can I create index for this SQL query?

table:

CREATE TABLE `deal` (
  `id` int(11) NOT NULL default '0',
  `site` int(11) NOT NULL default '0',
  `time` bigint(13) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `site` (`site`),
  KEY `time` (`time`,`site`)
) TYPE=MyISAM

开发者_如何转开发

sql query:

select * from `deal` where time>0 && site=8

I create index:time for this query,

but why this query always using index: site?

explain select * from `deal` where time>0 && site=8

output:

table    type    possible_keys   key    key_len    ref   rows   Extra

deal     ref     site,time       site   4          const 1      Using where

---

You need to create composite index site + time (yes, order matters).

So delete both indexes site and time now and create 2:

1. KEY site (site, time)
2. KEY time (time)