Indexes for Temporary tables Concept

Creating a Temporary table:

create table emp like employees

 This command creates emp table with the structure  like employees table

create table emp1  select * from employees

This command creates emp1 table with the structure and data  like employees table

you can create the table and add the index afterwards:

CREATE TEMPORARY TABLE temp_table(employeeNumber int)

ALTER TABLE temp_table ADD FULLTEXT INDEX (employeeNumber)

INSERT INTO temp_table SELECT * FROM regular_table

But the index will be, again, updated on every insert.
Probably the most efficient way would be to create the temp table, insert all, build index afterwards:

CREATE TEMPORARY TABLE temp_table

ALTER TABLE temp_table ADD FULLTEXT INDEX (employeeNumber)

ALTER TABLE temp_table DISABLE KEYS

INSERT INTO temp_table SELECT * FROM regular_table

ALTER TABLE temp_table ENABLE KEYS

Creating a MySQL Index - New Table:

CREATE TABLE employee_records

(

               name VARCHAR(50),

               employeeID INT, INDEX (employeeID)

);

Creating a MySQL Index - Existing Table:

CREATE TABLE employee_records2 (name VARCHAR(50), employeeID INT);

CREATE INDEX id_index ON employee_records2(employeeID);

ALTER TABLE  employee_records2 ADD INDEX (employeeID);

Droping a MySQL Index - Existing Table:

ALTER TABLE employee_records2   DROP INDEX (employeeID);

 Creating a multi-column MySQL table index:

CREATE INDEX idx_employee_records2  ON employee_records2 (name, employeeID);

The MySQL SHOW INDEX command :

show index from node;

This command shows the indexes from the database table named node