I intends to use indexes in MySQL to make SELECT queries faster. I don't know that how to create indexes in MySQL as I am unable to use phpMyAdmin. Can you explain, how to create unique key or index in MySQL without using phpMyAdmin etc?
MySQL allows to create unique key or index on single column as well as multi columns (combination of columns) of the table. To create unique index, MySQL provides CREATE TABLE, ALTER TABLE and CREATE UNIQUE INDEX statements. So here, we can use them to create single column unique index:
Take two examples to add a single column unique index while creating table:
CREATE TABLE buyers (
buyer_id INT UNSIGNED NOT NULL,
first_name CHAR(19) NOT NULL UNIQUE /* applying unique key index */,
last_name CHAR(19) NOT NULL,
age SMALLINT NOT NULL,
post_code SMALLINT NOT NULL
);
or
CREATE TABLE buyers (
buyer_id INT UNSIGNED NOT NULL,
first_name CHAR(19) NOT NULL,
last_name CHAR(19) NOT NULL,
age SMALLINT NOT NULL,
post_code SMALLINT NOT NULL,
UNIQUE idx_fname(first_name) /* applying unique index */
);
Take an example to add single column unique index in an existing table. We can do this with ALTER TABLE statement:
ALTER TABLE buyers ADD UNIQUE indx_fname (first_name);
/* Note: here "indx_fname" is the name of index being created.
It is optional, so you can omit this if you don't require */
Another example to add single column unique index in an existing. Here, we will use CREATE UNIQUE INDEX statement:
CREATE UNIQUE INDEX idx_fname ON buyers(first_name);
/* Note: here "indx_fname" is the name of index being created.
It is optional, so you can omit this if you don't require */