Re: Partial key

[noudshoorn]

************************************************************************************************************
In this part the table has an autoincrmentfiled. This field has to be a primary key with the name primary???
An index on name or departuredate is not used!?


SQL resultaat
Host: localhost
Database: evaco
Generatie Tijd: 15 Jun 2005 om 19:51
Gegenereerd door: phpMyAdmin 2.6.1-rc1 / MySQL 4.1.11-Debian_0.dotdeb.0-log
SQL-query: describe A;
Rijen: 4

Field Type Null Key Default Extra
name varchar(30) MUL
departuredate date MUL 0000-00-00
departuretime time 00:00:00
admin smallint(6) PRI NULL auto_increment

SQL resultaat
Host: localhost
Database: evaco
Generatie Tijd: 15 Jun 2005 om 19:52
Gegenereerd door: phpMyAdmin 2.6.1-rc1 / MySQL 4.1.11-Debian_0.dotdeb.0-log
SQL-query: show index from A;
Rijen: 6

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
A 0 PRIMARY 1 admin A 8 NULL NULL BTREE
A 1 name 1 name A 2 NULL NULL BTREE
A 1 name 2 departuredate A 8 NULL NULL BTREE
A 1 name 3 departuretime A 8 NULL NULL BTREE
A 1 departuredate 1 departuredate A 4 NULL NULL BTREE
A 1 departuredate 2 departuretime A 8 NULL NULL BTREE

SQL resultaat
Host: localhost
Database: evaco
Generatie Tijd: 15 Jun 2005 om 19:53
Gegenereerd door: phpMyAdmin 2.6.1-rc1 / MySQL 4.1.11-Debian_0.dotdeb.0-log
SQL-query: explain select * from A where name like 'N%';
Rijen: 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE A ALL name NULL NULL NULL 8 Using where

SQL resultaat
Host: localhost
Database: evaco
Generatie Tijd: 15 Jun 2005 om 19:55
Gegenereerd door: phpMyAdmin 2.6.1-rc1 / MySQL 4.1.11-Debian_0.dotdeb.0-log
SQL-query: explain select * from A where departuredate like '2005%';
Rijen: 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE A ALL departuredate NULL NULL NULL 8 Using where



*****************************************************
In this part the table hasn't an autoincrmentfield.
Now, an index on name or departuredate is used!?

SQL resultaat
Host: localhost
Database: evaco
Generatie Tijd: 15 Jun 2005 om 19:56
Gegenereerd door: phpMyAdmin 2.6.1-rc1 / MySQL 4.1.11-Debian_0.dotdeb.0-log
SQL-query: describe A;
Rijen: 3

Field Type Null Key Default Extra
name varchar(30) MUL
departuredate date MUL 0000-00-00
departuretime time 00:00:00

SQL resultaat
Host: localhost
Database: evaco
Generatie Tijd: 15 Jun 2005 om 19:57
Gegenereerd door: phpMyAdmin 2.6.1-rc1 / MySQL 4.1.11-Debian_0.dotdeb.0-log
SQL-query: show index from A;
Rijen: 5

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
A 1 name 1 name A NULL NULL NULL BTREE
A 1 name 2 departuredate A NULL NULL NULL BTREE
A 1 name 3 departuretime A NULL NULL NULL BTREE
A 1 departuredate 1 departuredate A NULL NULL NULL BTREE
A 1 departuredate 2 departuretime A NULL NULL NULL BTREE

SQL resultaat
Host: localhost
Database: evaco
Generatie Tijd: 15 Jun 2005 om 19:58
Gegenereerd door: phpMyAdmin 2.6.1-rc1 / MySQL 4.1.11-Debian_0.dotdeb.0-log
SQL-query: explain select * from A where name like 'N%';
Rijen: 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE A range name name 30 NULL 7 Using where; Using index

SQL resultaat
Host: localhost
Database: evaco
Generatie Tijd: 15 Jun 2005 om 19:59
Gegenereerd door: phpMyAdmin 2.6.1-rc1 / MySQL 4.1.11-Debian_0.dotdeb.0-log
SQL-query: explain select * from A where departuredate like '2005%';
Rijen: 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE A index departuredate name 36 NULL 8 Using where; Using index