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..

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).

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 ?

Abandoned wrote:
> And which index type can i use ? Btree, Rtree, Gist or Hash ?

You can use the ones that MySQL supports. To find out what they are RTFM

http://dev.mysql.com/doc/refman/5.0/...l-indexes.html

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 even (id, id2, d), which was what I suggested.

> or two different index (id) (id2) ?
It depends on what your data actually looks like and how you will search
through it.

If you will always search using at least id=x and id2=y, then the combined
index is much better. If you will occasionally search using only id2, then
build the index as (id2,id1) instead.
If you often search for id=x and id2=y, but you don't build a combined
index, then you are certainly wasting a resource.

> And how about unique ?
How can I tell. If id + id2 are always unique then I would build it as a
unique index to enforce the constraint.

You have not given enough information about your real data or your real
queries to make a proper suggestion. I can only speculate and tell you, if
your data is like this then do..., if it is like that then do..., and I
haven't got the time to go through all the options.

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
==================

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 ?

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
==================