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]
