Abandoned wrote:
> 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 ?
>
>

Yes, when you have an index, it must be updated on all insert
operations, as well as any update operations which affect one or more
columns in the index.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================