MySQL database table creation

Question

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)

Taras Soroka · Accepted Answer · 2015-05-30 19:19:42Z

1

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

answered May 30, 2015 at 19:19

Taras Soroka

1267 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4956971 Over a year ago

what is the benefit of multiple index? @Taras Soroka

Taras Soroka Over a year ago

General, with multiple indexes (for example ... KEY ind1 (column_1), KEY ind2 (column2), ..... ) you can index combinations of column values and speed up queries such as SELECT * from TABLE WHERE column1=X AND column_2=Y but in your case I really do not know what is mean of creating 3 indexes for 1 field.

Bacteria · Accepted Answer · 2015-05-30 19:25:13Z

1

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

edited May 30, 2015 at 19:25

answered May 30, 2015 at 19:18

Bacteria

8,63211 gold badges57 silver badges73 bronze badges

Comments

zedfoxus · Accepted Answer · 2015-05-31 03:48:06Z

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';

Collectives™ on Stack Overflow

MySQL database table creation

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related