Mysql: How do I index a database column

Hopefully, I can get answers for each database server.

For an outline of how indexing works check out: How does database indexing work?

 Answers

The following is SQL92 standard so should be supported by the majority of RDMBS that use SQL:

CREATE INDEX [index name] ON [table name] ( [column name] )

---

For python pytables, indexes don't have names and they are bound to single columns:

tables.columns.column_name.createIndex()

---

To create indexes following stuff can be used:

1. Creates an index on a table. Duplicate values are allowed:
   CREATE INDEX index_name ON table_name (column_name)
2. Creates a unique index on a table. Duplicate values are not allowed:
   CREATE UNIQUE INDEX index_name ON table_name (column_name)
3. Clustered Index
   CREATE CLUSTERED INDEX CL_ID ON SALES(ID);
4. Non-clustered index
   CREATE NONCLUSTERED INDEX NONCI_PC ON SALES(ProductCode);

---

1.

 CREATE INDEX name_index ON Employee (Employee_Name)

2. On a multi column

   CREATE INDEX name_index ON Employee (Employee_Name, Employee_Age)
   

---

An index is not always needed for all the databases. For eg: Kognitio aka WX2 engine doesn't offer a syntax for indexing as the database engine takes care of it implicitly. Data goes on via round-robin partitioning and Kognitio WX2 gets data on and off disk in the simplest possible way.