Indexes in MySQL
Managing Indexes in MySQL –
Index is a method/process that MySQL uses to speed up searches and reduce the time it takes to execute complex queries. Index provides an organized list of pointers to the actual data. Due to indexing, when MySQL is executing a query, it does not have to scan each table in its entirety to locate the correct data, but it can instead scan the index, which results in quicker and more efficient access…
MySQL supports five type of indexes that can be created on a table….
1) Primary Key – The value or set of values should be unique in the columns on which the primary key is defined. NULL value is not allowed. Table can include only one primary key.
2) Foreign Key – Enforces the relationship between the referencing columns in the child table where the foreign key is defined and the referenced columns in the parent table.
3) Regular – A basic index that permits duplicate values and null values in the columns on which the index is defined…
4) Unique – Requires that each value or set of values be unique in the columns on which the index is defined. Unlike primary key indexes,null values are allowed.
5) Full-text – Supports full text searches of the values in the columns on which the index is defined. A full text permits duplicate values and null values in those columns. A full-text index can be defined only on MyISAM tables and only on CHAR, VARCHAR columns…
How to create indexes –
(a) In below query for creating table. We are defining two indexes one primary key and other is unique.
mysql> CREATE TABLE market (orderID INT(5), modelID INT(5), Name VARCHAR(40), PRIMARY KEY(orderID), UNIQUE (orderID,modelID));
Query OK, 0 rows affected (0.00 sec)
mysql>
(b) In below query for creating table. We are defining Regular indexing –
mysql> CREATE TABLE market (orderID INT(5), Name VARCHAR(40), INDEX(orderID) );
Query OK, 0 rows affected (0.00 sec)
mysql>
(c) How to add indexes to existing table –
mysql> CREATE TABLE market (orderID INT(5), Name VARCHAR(40));
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE market ADD PRIMARY KEY (orderID);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
(d) How to create index by using “CREATE INDEX” –
mysql> CREATE TABLE market (orderID INT(5), Name VARCHAR(40));
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE INDEX myindex ON market (orderID);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
** How to drop index –
mysql> DROP INDEX myindex ON market;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
(e) How to get information about your Databases and tables –
** Below SQL commands can be used to view the database definition for specific database.
mysql> SHOW CREATE DATABASE zabbix;
+———-+——————————————————————-+
| Database | Create Database |
+———-+——————————————————————-+
| zabbix | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+———-+——————————————————————-+
1 row in set (0.00 sec)
mysql>
** SQL command to view table definition –
mysql> SHOW CREATE TABLE market;
+——–+———————————————+
| Table | Create Table |
+——–+———————————————+
| market | CREATE TABLE `market` (
`orderID` int(5) default NULL,
`Name` varchar(40) default NULL,
KEY `myindex` (`orderID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+——–+———————————————+
1 row in set (0.00 sec)
mysql>
** How to describe the table structure –
mysql> SHOW COLUMNS FROM market;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| orderID | int(5) | YES | | NULL | |
| Name | varchar(40) | YES | | NULL | |
+———+————-+——+—–+———+——-+
2 rows in set (0.01 sec)
mysql> DESC market;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| orderID | int(5) | YES | | NULL | |
| Name | varchar(40) | YES | | NULL | |
+———+————-+——+—–+———+——-+
2 rows in set (0.00 sec)
** How to views indexes defined on perticular table –
mysql> SHOW INDEX FROM market;
Empty set (0.00 sec)
mysql> CREATE INDEX myindex ON market (orderID);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM market;
+——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
| market | 1 | myindex | 1 | orderID | A | NULL | NULL | NULL | YES | BTREE | |
+——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
1 row in set (0.00 sec)
mysql>
Thank you,
Arun Bagul
