Indexes, AUTO_INCREMENT ...

Ok, I just don't understand it.. what are the practical uses of indexes,
AUTO_INCREMENT etc.. how are they supposed to work?

Re: Indexes, AUTO_INCREMENT ...

Flaming Corncob wrote:
> Ok, I just don't understand it.. what are the practical uses of indexes,
> AUTO_INCREMENT etc.. how are they supposed to work?

The sql server will take care of the increment of the value, without you need
first check up whats the highest value, then add one to it and then insert the
new data to the table. This can lead to that more than one will try to insert
data with the same value, which leads to problems.
With the auto increment, you don't need to insert the value when you insert
data into your table, it will automatically be inserted and adjusted, and no
risk for two to get the same value.

This is useful when you want to give unique id-numbers for all rows in a table.



//Aho

Re: Indexes, AUTO_INCREMENT ...

In article <4km59fFcpnbrU1@individual.net>,
"J.O. Aho" wrote:

> Flaming Corncob wrote:
> > Ok, I just don't understand it.. what are the practical uses of indexes,
> > AUTO_INCREMENT etc.. how are they supposed to work?
>
> The sql server will take care of the increment of the value, without you need
> first check up whats the highest value, then add one to it and then insert
> the
> new data to the table. This can lead to that more than one will try to insert
> data with the same value, which leads to problems.
> With the auto increment, you don't need to insert the value when you insert
> data into your table, it will automatically be inserted and adjusted, and no
> risk for two to get the same value.
>
> This is useful when you want to give unique id-numbers for all rows in a
> table.
>
>
>
> //Aho

Sounds like what I already read.. just can't picture the practical use.
Maybe because it's not what I'm looking for?

Re: Indexes, AUTO_INCREMENT ...

Flaming Corncob wrote:
> In article <4km59fFcpnbrU1@individual.net>,
> "J.O. Aho" wrote:
>
>> Flaming Corncob wrote:
>>> Ok, I just don't understand it.. what are the practical uses of indexes,
>>> AUTO_INCREMENT etc.. how are they supposed to work?
>> The sql server will take care of the increment of the value, without you need
>> first check up whats the highest value, then add one to it and then insert
>> the
>> new data to the table. This can lead to that more than one will try to insert
>> data with the same value, which leads to problems.
>> With the auto increment, you don't need to insert the value when you insert
>> data into your table, it will automatically be inserted and adjusted, and no
>> risk for two to get the same value.
>>
>> This is useful when you want to give unique id-numbers for all rows in a
>> table.

> Sounds like what I already read.. just can't picture the practical use.
> Maybe because it's not what I'm looking for?

I don't know what you are looking for, but AUTO_INCREMENT is really useful for
row id's, if you don't use any row identification, then it's not useful for you.


//Aho

Re: Indexes, AUTO_INCREMENT ...

In article <4kmaetFc7gcoU1@individual.net>,
"J.O. Aho" wrote:

> Flaming Corncob wrote:
> > In article <4km59fFcpnbrU1@individual.net>,
> > "J.O. Aho" wrote:
> >
> >> Flaming Corncob wrote:
> >>> Ok, I just don't understand it.. what are the practical uses of indexes,
> >>> AUTO_INCREMENT etc.. how are they supposed to work?
> >> The sql server will take care of the increment of the value, without you
> >> need
> >> first check up whats the highest value, then add one to it and then insert
> >> the
> >> new data to the table. This can lead to that more than one will try to
> >> insert
> >> data with the same value, which leads to problems.
> >> With the auto increment, you don't need to insert the value when you
> >> insert
> >> data into your table, it will automatically be inserted and adjusted, and
> >> no
> >> risk for two to get the same value.
> >>
> >> This is useful when you want to give unique id-numbers for all rows in a
> >> table.
>
> > Sounds like what I already read.. just can't picture the practical use.
> > Maybe because it's not what I'm looking for?
>
> I don't know what you are looking for, but AUTO_INCREMENT is really useful
> for
> row id's, if you don't use any row identification, then it's not useful for
> you.
>
>
> //Aho

I'm in the process of coding a forum. What I was looking for was
something that keeps record of each folder/topic/subtopic/post, like an
index number that is unique for each. When something is moved, edited or
deleted it would make it easy to "refer" to a number and in the case for
example it is deleted anything under that referencing that number is
also deleted, and the number could be re-used. I thought indexes and
such was the way to go, but I can't seem to make heads or tails what's
going on.

Re: Indexes, AUTO_INCREMENT ...

Flaming Corncob wrote:

> I'm in the process of coding a forum. What I was looking for was
> something that keeps record of each folder/topic/subtopic/post, like an
> index number that is unique for each. When something is moved, edited or
> deleted it would make it easy to "refer" to a number and in the case for
> example it is deleted anything under that referencing that number is
> also deleted, and the number could be re-used. I thought indexes and
> such was the way to go, but I can't seem to make heads or tails what's
> going on.

You can have two columns, MessageID (int auto_increment) and ParentID (int),
for the first message the ParentID is 0, as it don't have a parent. All
replies to the first message in a thread will then have MessageID of the first
post as the ParentID.

When listing messages in a thread you sort them after MessageID, this will
give the flow in the same way as in phpBB (most popular forum used on the
net), if a message (reply) will be deleted you don't need to do anything extra
for make the thread to flow normally. If you delete a whole thread, it's quite
simple to delete all of them too.

Reusing ID-numbers can be a bad idea, specially as there are always someone
who does link to a thread from a web page, better in that case give a "error"
message than serve a thread that has nothing to do with the original thread.



//Aho

Re: Indexes, AUTO_INCREMENT ...

Flaming Corncob wrote:
> In article <4kmaetFc7gcoU1@individual.net>,
> "J.O. Aho" wrote:
>> Flaming Corncob wrote:
>>> In article <4km59fFcpnbrU1@individual.net>,
>>> "J.O. Aho" wrote:
>>>
>>>> Flaming Corncob wrote:
>>>>> Ok, I just don't understand it.. what are the practical uses of
>>>>> indexes, AUTO_INCREMENT etc.. how are they supposed to work?
>>>> The sql server will take care of the increment of the value,
>>>> without you need
>>>> first check up whats the highest value, then add one to it and
>>>> then insert the
>>>> new data to the table. This can lead to that more than one will
>>>> try to insert
>>>> data with the same value, which leads to problems.
>>>> With the auto increment, you don't need to insert the value when
>>>> you insert
>>>> data into your table, it will automatically be inserted and
>>>> adjusted, and no
>>>> risk for two to get the same value.
>>>>
>>>> This is useful when you want to give unique id-numbers for all
>>>> rows in a table.
>>
>>> Sounds like what I already read.. just can't picture the practical
>>> use. Maybe because it's not what I'm looking for?
>>
>> I don't know what you are looking for, but AUTO_INCREMENT is really
>> useful for
>> row id's, if you don't use any row identification, then it's not
>> useful for you.
>
> I'm in the process of coding a forum. What I was looking for was
> something that keeps record of each folder/topic/subtopic/post, like
> an
> index number that is unique for each. When something is moved,
edited
> or deleted it would make it easy to "refer" to a number and in the
> case for example it is deleted anything under that referencing that
> number is
> also deleted, and the number could be re-used.

Reusing? Why? How many topics/posts do you expect? You'll have to be
very,very,very popular to run out of integers for a unique post. And
you shouldn't reuse them. For instance: some posts something, gets id
1234, and you can vies the post by viewpost.php?id=1234. It is some
usefull information, and some people link to it. Later on, the post is
deleted. No problem, in your code you take care of that: you inform
the user the post is no longer there. Would you reuse the number, the
link works again, and will show a post that has absolutely nothing to
do with the first post. People following the old links won't think
very highly of you forum.

Moral of the story: if possible, don't reuse unique identifiers.

> I thought indexes and
> such was the way to go, but I can't seem to make heads or tails
what's
> going on.

The right primary keys and indexed will enable your database to
perform queries a lot faster and efficient then they normally would.
Creating a unique id (primary key) will also have major advantages:
one simpel number to refer to, instead of searching on title/time/user
etc.

Grtz,
--
Rik Wasmus

Re: Indexes, AUTO_INCREMENT ...

In article <497e6$44e62c0b$8259c69c$23743@news2.tudelft.nl>,
"Rik" wrote:

> Reusing? Why? How many topics/posts do you expect? You'll have to be
> very,very,very popular to run out of integers for a unique post. And
> you shouldn't reuse them. For instance: some posts something, gets id
> 1234, and you can vies the post by viewpost.php?id=1234. It is some
> usefull information, and some people link to it. Later on, the post is
> deleted. No problem, in your code you take care of that: you inform
> the user the post is no longer there. Would you reuse the number, the
> link works again, and will show a post that has absolutely nothing to
> do with the first post. People following the old links won't think
> very highly of you forum.

Never did occur to me. That's why I post.. to ring in some sense.