mySQL CREATE TABLE UNIQUE INDEX

Question

Good morning. I'm reading some examples of creating tables in mySQL but I'm not understanding the last statement on the following code: CREATE TABLE 'company_test', 'employee'( ... UNIQUE INDEX 'department_id_UNIQUE' ('department_id' ASC) VISIBLE) what does this UNIQUE INDEX 'department_id_UNIQUE ('department_id' ASC) VISIBLE do? I went searching everywhere that could explain this but I could only find exemples using CREATE INDEX outside CREATE TABLE, what does an index do and somebody can explain in detail that statement? If you could show me some documents talking about that I would be grateful.

Answer 1

1. What does this statement do? UNIQUE INDEX 'department_id_UNIQUE ('department_id' ASC) VISIBLE

This statement creates unique index on department_id column of employee table in your example. Index can be created during table creation as well as at later time after table is created. MySQL Create table syntax: create table Index creation after table creation: create index

2. What does index do? Index improves read performance at the cost of write performance.In you example alongwith index it creates unique constraint on columns which will prevent duplicate values for department_id field. This post explains how database indexing works in details.

Answer 2

UNIQUE INDEX 'department_id_UNIQUE ('department_id' ASC) VISIBLE

UNIQUE -- an index BTree is built and maintained; a uniqueness constraint is established.
INDEX -- optional (redundant) syntax
'...' -- optional arbitrary name for this index.  A default will be provided if left out.
(...) -- The column(s) in the index.  The combination is unique; the BTree is ordered by them.
ASC -- Ascending (as opposed to DESC: Descending)
VISIBLE -- below

VISIBLE is a new keyword. Some products tack this on; some other products see it as a syntax error. If that is a problem, simply remove the word.

The purpose of VISIBLE and INVISIBLE is thus. CREATEing and DROPping an index is potentially a costly action. If you want to remove the index (to save disk space), it can be advantageous to make it INVISIBLE to see if any query slows down drastically. That might tell you that the index is needed. Then you can quickly make it VISIBLE again. Else you can DROP it.

VISIBLE is the default; leaving it off is OK.