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.
>Solution :
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.