MySQL Forums
Forum List  »  Performance

Re: Partial key
Posted by: noudshoorn
Date: June 15, 2005 12:06PM

************************************************************************************************************
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

Options: ReplyQuote


Subject
Views
Written By
Posted
3555
June 11, 2005 12:03PM
1815
June 14, 2005 10:42AM
1948
June 14, 2005 02:16PM
1688
June 15, 2005 02:21AM
1688
June 15, 2005 05:16AM
1839
June 15, 2005 06:24AM
Re: Partial key
5041
June 15, 2005 12:06PM
1747
June 16, 2005 05:07AM
1716
June 16, 2005 07:57AM
1719
June 16, 2005 08:30AM
1774
June 16, 2005 08:50AM
1619
June 16, 2005 10:21AM
2723
June 16, 2005 03:00PM
1721
June 17, 2005 12:00AM
1635
June 17, 2005 01:28AM
1619
June 20, 2005 01:21AM
1812
June 20, 2005 06:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.