CODEWITHSUNDEEP
mysqlsqldatabase
user4956971
user4956971

Reputation: 11

MySQL database table creation

In a MySQL database, a table has been created as follows:

CREATE TABLE IF NOT EXISTS `sub` (
`s_id` int(3) NOT NULL AUTO_INCREMENT COMMENT 'Standard id',
`std` int(10) NOT NULL COMMENT 'Standard',
`sub_nm` varchar(25) NOT NULL COMMENT 'Subject Name',
 PRIMARY KEY (`s_id`),
 KEY `sub_nm` (`sub_nm`),
 KEY `sub_nm_2` (`sub_nm`),
 KEY `sub_nm_3` (`sub_nm`)
 )    ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='All Subjects with
     corresponding standerds.' AUTO_INCREMENT=21 ;

My question is, what is meant by the last three KEY values? I mean: KEY sub_nm (sub_nm), KEY sub_nm_2 (sub_nm), and KEY sub_nm_3 (sub_nm)

Upvotes: 1

Views: 52

Answers (3)

zedfoxus
zedfoxus

Reputation: 37029

Previous answers have already addressed your question that KEY and INDEX keywords are synonymous. In addition to that, here are a couple of examples.

Create an index by using INDEX or KEY keyword during table creation

CREATE TABLE IF NOT EXISTS `sub` (
  `s_id` int(3) NOT NULL AUTO_INCREMENT COMMENT 'Standard id',
  `std` int(10) NOT NULL COMMENT 'Standard',
  `sub_nm` varchar(25) NOT NULL COMMENT 'Subject Name',
   PRIMARY KEY (`s_id`),
   INDEX `sub_nm` (`sub_nm`),
   INDEX `sub_nm_2` (`sub_nm`),
   INDEX `sub_nm_3` (`sub_nm`)
 );

It is not wise to create 3 indexes on the same field. One index is enough on sub_nm.

Create an index after table creation

CREATE TABLE IF NOT EXISTS `sub` (
  `s_id` int(3) NOT NULL AUTO_INCREMENT COMMENT 'Standard id',
  `std` int(10) NOT NULL COMMENT 'Standard',
  `sub_nm` varchar(25) NOT NULL COMMENT 'Subject Name',
   PRIMARY KEY (`s_id`)
 );

 create index sub_nm on `sub`(sub_nm);
 -- create key sub_nm2 on `sub`(sub_nm); WILL ERROR OUT
 alter table `sub` add index sub_nm3 (sub_nm); 
 alter table `sub` add key sub_nm4 (sub_nm);

Notice the different ways one can create index on the table. I generally use index name like this: idx_tablename_fieldname (e.g. idx_sub_sub_nm).

Index on sub_nm may improve performance of queries that utilize sub_nm in filtering, sorting and grouping. EXPLAIN helps in identifying if the database believes an index will be used. If database doesn't use a query that you very strongly believe it should, index hints can be provided in the query like this:

select s_id from sub use index (idx_sub_sub_nm) where sub_nm = 'test';

Upvotes: 0

Bacteria
Bacteria

Reputation: 8596

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

For more

Upvotes: 1

Taras Soroka
Taras Soroka

Reputation: 126

With this instruction KEY sub_nm (sub_nm), KEY sub_nm_2 (sub_nm), KEY sub_nm_3 (sub_nm) you create 3 indexes for sub_nm column named sun_nm, sub_nm_2 and sub_nm_3

Upvotes: 1

Related Questions