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.
jstucklex@attglobal.net
==================