redho home | products | services

Programming Forums


Community for Java, PHP, Perl C, ASP and Python programmers
Wednesday 18 September 2019 18:31

Ask your IT question here

Creating Indexes



 
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Programming Forums -> Database discussion forum
View previous topic :: View next topic  
Author Message
gilbertsavier
New Programmer


Joined: 02 Jul 2009
Posts: 21
Hello,
Database indexes help to speed the retrieval of data from MySQL database server faster. When retrieving the data, MySQL first check whether the indexes exists; If yes it will use index to select exact physical corresponding rows without scanning the whole table.

In general, it is suggested that you should put indexes on columns you usually use in retrieval such as primary key columns and columns used in join and sorts. Why not index every column? The most significant is that building and maintaining an indexes tables take time and storage space on database.

Usually you create indexes when creating tables. Any column in creating table statement declared as PRIMARY KEY, KEY, UNIQUE or INDEX will be indexed by MySQL. In addition, you can add indexes to the tables which has data. The statement to create index in MySQL as follows:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
USING [BTREE | HASH | RTREE]
ON table_name (column_name [(length)] [ASC | DESC],...)

First you specify the index based on the table types or storage engine:

* UNIQUE means MySQL will create a constraint that all values in the index must be distinct. Duplicated NULL is allowed in all storage engine except BDB.
* FULLTEXT index is supported only by MyISAM storage engine and only accepted columns which have data type is CHAR,VARCHAR or TEXT.
* SPATIAL index supports spatial column and available in MyISAM storage engine. In addition, the column value must not be NULL.

Then you name the index using index types such as BTREE, HASH or RTREE also based on storage engine. Here are the list:
Storage Engine Allowable Index Types
MyISAM BTREE, RTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH


Finally you declare which column on which table using the index.

In our sample database you can add index to officeCode column on employees table to make the join operation with office table faster as follows:

CREATE INDEX officeCode ON employees(officeCode)
_________________
Thanks & regards
Lokananth

Live Chat Software By miOOt

Reply with quote
 
Melville
New Programmer


Joined: 10 Dec 2008
Posts: 14
Location: USA
Hello,


this is a nice Article upon Creating Indexes.


I liked it and i was searching about this matter for project.

you help me a lot

Thanks a Lot !!!
_________________
anti keylogger

Reply with quote
 
Page 1 of 1
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Programming Forums -> Database discussion forum


Dubai Forums - Expat Help | Vegan Forum | Java Programming | 3d Design Resources | 3d Forum | 3D Jobs | 3D Textures | Paris Forum | Europe Forum
Dubai Classifieds | Dubai Property | Jobs in Dubai | Free London Classifieds | Jobs in London UK

High Quality, Custom 3d animation and Web Design solutions Royal Quality Web Hosting Services Vegetarian and Animal Rights news

© 2018 RedHo