how can I create index for this SQL query?

Question

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

zerkms · Accepted Answer · 2011-07-08 04:29:27Z

5

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

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

KEY site (site, time)
KEY time (time)

answered Jul 8, 2011 at 4:29

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Koerr Over a year ago

thanks zerkms, KEY site (site, time) can completely replace the KEY site (site)? (I also need select * from deal where site=123)

zerkms Over a year ago

@Zenofo: yes, it is called "leftmost". So with index a+b+c you don't need to have indexes a and a+b, since it covers them with its left part

Collectives™ on Stack Overflow

how can I create index for this SQL query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related