Page 1 of 1

Table design and index/primary key problem

Posted: Thu Apr 15, 2004 7:26 pm
by Pozor
Hello,

i have the followed table:

Code: Select all

id        int 11 unsigned auto_increment Primary Key     changes never
sess1     varchar 32                                     changes very often
sess2     varchar 32                                     changes very often
sess3     varchar 32                                     changes very often
sess4     varchar 32                                     changes very often
userid    int 11 unsigned                                changes rearely/never
secure    tinyint 1 unsigned                             changes rearely
variables text                                           changes sometime
timestamp timestamp 14                                   changes very often
i made an additional column id, because a primary key should not/never change.
it depend on whitch mode the session management is running, whitch columns are changing...

the worst scenario is like described above..
it doesn't make sens to make an index over sess1,sess2,sess3,sess4 because they change very often, does it?
shouldn't i have an index over some columns, should i?

can someone help me or give me an advice for this problem?

greez Pozor

Posted: Thu Apr 15, 2004 7:29 pm
by markl999
The general rule is to add an index for columns you'll often use in the WHERE part of a query. There are exceptions, but as a general rule it works ok.

Posted: Thu Apr 15, 2004 7:39 pm
by Pozor
Hello,

ok that is what i already know,

use an index like index (col1,col2) for where col1 = 1 AND col2=2 or so

whats about where col1 = 2 OR col2 = 2 ... i think then you use index col1 index col2.

but this makes only sense (for speed issue) when this cols not change very often, because then the indeces must be updated every time a change occur...

this is the main point im looking for, shouldn't i make an index, altough i make every time a change, should i?


greez Pozor

Posted: Thu Apr 15, 2004 7:42 pm
by markl999
As far as i understand it the indexes only come into play when searching, so updating/changing won't be affected by the index (apart from the WHERE part).
This is as much as i know about database indexes so if you want more in depth information i'm sure someone more knowledgable will step in ;)
(i hope so, then i might learn more about them :o)

Posted: Thu Apr 15, 2004 8:30 pm
by Pozor
Hello,

ok so far so good. However indeces have to be updated when changes occur! Otherwise they wont be actual. This is what i mean. it doesn't make much sense when you make an index over a column that changes very often, and my columns (sess 1 to 4) change every time (one select, then one update and sometimes an insert).

greez Pozor

PS: i read some good stuff in this forum, but i can't find the solution or a rule for this particular problem...