On Oct 28, 3:39 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Abandoned wrote:
> > On Oct 28, 3:27 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > wrote:
> >> Abandoned wrote:
> >>> Hi..
> >>> I want to do index in database.
> >>> My table:
> >>> id(int) | id2(int) | w(int) | d(int)
> >>> My query:
> >>> select id, w where id=x and id2=y (sometimes and d=z)
> >>> I have too many insert and select operation on this table.
> >>> And which index type can i use ? Btree, Rtree, Gist or Hash ?
> >>> Also I want to unique (id, id2)..
> >>> Now this is my index. is it give me good performance ?
> >>> CREATE UNIQUE INDEX ind1 ON test USING btree (id, id2)
> >>> CREATE INDEX ind2 ON test USING btree (id)
> >>> CREATE INDEX ind3 ON test USING btree (id2)
> >>> CREATE INDEX ind4 ON test USING btree (w)
> >>> CREATE INDEX ind5 ON test USING btree (d)
> >>> I'm too sorry my bad english.
> >>> King regards..
> >> If you only ever use d=z in conjunction with id=x and id2=y, then I would go
> >> for a single index of
> >> (id, id2, d).
> >> Depending on the structure of your data, you might simply make this the only
> >> index (primary key).
>
> > Which is the give me better performance ?
> > Single (id, id2)
> > or two different index (id) (id2) ?
> > And how about unique ?
>
> It depends on what else you have going on in your application. You
> can't determine index usage based on one query - you need to look at
> your entire application, including insert and update statements, as all
> will be affected by indexes.
>
> Some things to remember, though. MySQL will currently not use two
> different indexes for a query. So if you just have (id) and (id2),
> MySQL will be able to index on one column in a query, but not the other.
>
> An index on (id, id2) will allow it to index both columns in a single
> query, and as a bonus, it can be used to index (id). However, it won't
> be able to use this to index on (id2).
>
> If you do need to index on (id2), you could create indexes on (id, id2)
> and (id2). This may seem redundant, but it really isn't. However, this
> will affect all insert statements and update statements which affect (id2).
>
> Hope this helps your understanding.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================
Thanks..I understand.
Are indexes to slow down the insert operation ?