If for example I have a table with the 3 columns: name,age,sex & I
build an index table
"index table" ? There is no such thing.
on the column 'name'. how can i get result of
the other rows (i.e age and )
other rows? You mean other columns?
from the main table
What main table?
if i 'select' a name from the main table? In short, what is the structure(or should i say syntax?) of a selected statement for an index table?
There is no "index table". There are only tables and indexes. Any index is related to one or more columns in a table, but it is transparent when it comes to the usage of the table.
Let's say your example table is named 'names'. It could be created like this:
CREATE TABLE names (
name varchar(30) NOT NULL,
age tinyint not null,
sex enum('f','m','unknown') default 'unknown'
);In general 'age' is not a good column, it is better to store year of birth or date of birth. That way you don't need to know the year of data collection to calculate the current age... but this may not be relevant in all applications.
Lets insert a few records:
INSERT INTO names VALUES
('ken',25,'m'),
('barbie',22,'f');Lets select something:
mysql> SELECT age FROM names WHERE name = 'barbie'; +-----+ | age | +-----+ | 22 | +-----+ 1 row in set (0.03 sec)
Now, if you add an index on this table, _nothing_ changes in the way you use the table. Adding an index with the ALTER TABLE statement:
mysql> ALTER TABLE names ADD INDEX (name); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT age FROM names WHERE name = 'barbie'; +-----+ | age | +-----+ | 22 | +-----+ 1 row in set (0.03 sec)
Note that the index we created was not unique. Most tables should have some unique column, and this column should be defined as a PRIMARY KEY. You can also define a UNIQUE index on a column or multiple columns in addition to the primary key, if needed. Primary keys and unique keys are used to prevent duplicates in your tables. A non-unique index, like the one we created above, will only increase the speed of the queries when that particular indexed column is used in the WHERE clause. In this case, with two rows, it has no use. It will not prevent duplicates, like a primary key or an unique index would. So you should probably design your table in one of the following ways, depending on your needs:
# non-unique name, multiple barbies may exist:
CREATE TABLE names (
id integer NOT NULL PRIMARY KEY,
name varchar(30) NOT NULL,
age tinyint NOT NULL ,
sex enum('f','m','unknown') NOT NULL DEFAULT 'unknown',
INDEX (name)
);# unique name, no relations
CREATE TABLE names (
name varchar(30) NOT NULL PRIMARY KEY,
age tinyint NOT NULL ,
sex enum('f','m','unknown') NOT NULL DEFAULT 'unknown'
);# unique name, relates to other tables (using id)
CREATE TABLE names (
id integer NOT NULL PRIMARY KEY,
name varchar(30) NOT NULL,
age tinyint NOT NULL,
sex enum('f','m','unknown') NOT NULL DEFAULT 'unknown',
UNIQUE (name)
);Further reading:
<URL: http://dev.mysql.com/doc/mysql/en/select.html > <URL: http://dev.mysql.com/doc/mysql/en/create-table.html > <URL: http://dev.mysql.com/doc/mysql/en/alter-table.html >
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
