how do I create Index for this SQL query? (Mysql)

Question

my table field type set: (MyISAM)

`expired` tinyint(1) value:0~1
`site`    int(11)    value:0~3000
`area`    int(11)    value:0~1050
`endtime` bigint(13) value:timestamp(like:1285779723799)

I want run this SQL:

select * from `deal` 
  where `expired`=1 && `site`=17 && `area`=108 
  order by `endtime` DESC limit 0,10

how do I create Index for this sql is the fastest?

When it comes to indexes you generally want to index what you search against frequently. Since you don't search against endtime you won't want to index this. Also if you only search against a field here and there I would exclude that as well...I'm no sql expert, but has been my understanding of indexing practices. — Jared
– Jared, Commented Nov 9, 2011 at 2:59

James.Xu · Accepted Answer · 2011-11-09 02:52:04Z

2

you query included all columns... then try this:

alter table test add index idx1(site, area, expired, endtime);

answered Nov 9, 2011 at 2:52

James.Xu

8,2975 gold badges27 silver badges36 bronze badges

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

13 Comments

Jared Over a year ago

I don't think this is a good solution....if you index everything you might as well have not indexed it (from my understanding)

user166390 Over a year ago

Depending upon the usage of expired/endtime, they may be "overkill".

user166390 Over a year ago

@Jared It depends on the query. The proposed index would "cover" the query conditional (but not necessarily the result set!). However, if the query was just looking for "expired" records, then that index would be useless ;-) There is indeed issues with over-indexing and, depending upon the data and usage, it might not be "best" to index over that much of it.

James.Xu Over a year ago

@Zenofo it has to be checked in two perspectives. 1. your query pattern, you know one compound index can cover all the left-prefix columns. 2. if the 1st perspective is not your concern, then i think put the column whose value space the biggest at first, because it is more selective.

James.Xu Over a year ago

@Zenofo By put the most selective column in the first, you can quickly minimize the result row count -- less disk reading, less disk seeking.

|

Collectives™ on Stack Overflow

how do I create Index for this SQL query? (Mysql)

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related