Dear Vivian Wang:
This depends a little upon what version you are using,
===
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 77 to server version: 4.1.0-alpha-max-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql select version();
+---+
| version() |
+---+
| 4.1.0-alpha-max-debug |
+---+
1 row in set (0.00 sec)
===
And also it does matter if you'd later like to add some foreign or
primary key, to the table whatever table type eg MyISAM, InnoDB ...
After the table is created.
Foreign keys however has to be indexed before they are created,
Indexing Tables increases the performance of a query if you use some
kind of JOIN ( or simply a comma in the from clause ) between multiple
tables, it does exist several table types, and column data types with
some attributes, which sometimes permit or deny indexing.
The table types behave differently
When you index their identifiers.
I'm primarily interested in indexing a column integer key data type,
Which should be a foreign key with reference to some other table.
But I have never ever succeeded in this, with MySQL.
The main problem lies according to what I feel in
How NULL values are treated.
But this is something I guess,
no table can contain any key with only NULL values.
In MySQL we have to index a foreign key before we are creating or using
it.
The datatype and column type has to be some integer, or string
datatype,
The column type has to have some attribute NOT NULL,
In order to prevent NULL values.
Even fulltext indexes exists for some table types.
I find this topic quite good explained in the excellent Book
Written by Mr. Paul DuBois, which replies here quite frequently.
Said easily if it helps,
A key can be indexed,
but it can also have some inherent meaning without ever being indexed.
Some database scientists believe that for some table types,
indexed columns can be NULL as long as the index is not a PK.
But I have never dared to challenge MySQL with anything like that.
Indexing speeds up the query performance of a select statement,
if you succeed in getting your database program started.
I hope ?
Please correct me if I'm wrong,
especially all of you who really understand
some of what I'm trying to express.
=
-Ursprüngliche Nachricht-
Von: Vivian Wang [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 2. September 2003 20:45
An: [EMAIL PROTECTED]
Betreff: different between index and key when create table
Can anyone tell me what is different between index and key when creating
table?
like this situation:
create table info ( fname char(9), lname char (15), address char(30),
index(lname));
or
create table info ( fname char(9), lname char(15), address char(30),
key(lname));
mysql DROP TABLE IF EXISTS info ;
Query OK, 0 rows affected (0.00 sec)
mysql CREATE TABLE info
- (
- fname char(9),
- lname char (15),
- address char(30),
- index(lname)
- );
Query OK, 0 rows affected (0.01 sec)
mysql DESCRIBE info;
+-+--+---+--+-+-+---
+
| Field | Type | Collation | Null | Key | Default | Extra
|
+-+--+---+--+-+-+---
+
| fname | char(9) | latin1_swedish_ci | YES | | NULL|
|
| lname | char(15) | latin1_swedish_ci | YES | MUL | NULL|
|
| address | char(30) | latin1_swedish_ci | YES | | NULL|
|
+-+--+---+--+-+-+---
+
3 rows in set (0.00 sec)
mysql
mysql DROP TABLE IF EXISTS info ;
Query OK, 0 rows affected (0.02 sec)
mysql CREATE TABLE info
- (
- fname char(9),
- lname char(15),
- address char(30),
- key(lname)
- );
Query OK, 0 rows affected (0.00 sec)
mysql DESCRIBE info;
+-+--+---+--+-+-+---
+
| Field | Type | Collation | Null | Key | Default | Extra
|
+-+--+---+--+-+-+---
+
| fname | char(9) | latin1_swedish_ci | YES | | NULL|
|
| lname | char(15) | latin1_swedish_ci | YES | MUL | NULL|
|
| address | char(30) | latin1_swedish_ci | YES | | NULL|
|
+-+--+---+--+-+-+---
+
3 rows in set (0.00 sec)
As you can see the only difference is 10 milliseconds in creating,
And 20 milliseconds in dropping. ;-)
I need some more queries ;-)
Like
insert into table ... values ...
and
select from ... where ...
As is, I'll get an empty set,
mysql SELECT * FROM info;
Empty set (0.00 sec)
Yours Sincerely
Morten Gulbrandsen
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
