MySQL Alter Table Statement Exercises: Add an index named indx_job_id on job_id column in the table job_history

MySQL Alter Table Statement: Exercise-14 with Solution

Write a MySQL statement to add an index named indx_job_id on job_id column in the table job_history.

Here is the structure of the table job_history.

mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID   | int(11) | NO   | PRI | NULL    |       |
| START_DATE    | date    | YES  |     | NULL    |       |
| HIRE_DATE     | date    | YES  |     | NULL    |       |
| JOB_ID        | int(11) | NO   |     | NULL    |       |
| DEPARTMENT_ID | int(11) | NO   |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+

Here is the index file of job_history table.

mysql> SHOW INDEXES FROM job_history;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| job_history |          0 | PRIMARY  |            1 | EMPLOYEE_ID | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Code:

 -- This SQL statement is used to alter the 'job_history' table by adding an index.
-- An index named 'indx_job_id' is being added to the 'job_id' column.

ALTER TABLE job_history

-- Add an index named 'indx_job_id' to the 'job_id' column in the 'job_history' table.
ADD INDEX indx_job_id(job_id);

Let execute the above code in MySQL command prompt

Now see the structure of the table job_history after being altered.

mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID   | int(11) | NO   | PRI | NULL    |       |
| START_DATE    | date    | YES  |     | NULL    |       |
| HIRE_DATE     | date    | YES  |     | NULL    |       |
| JOB_ID        | int(11) | NO   | MUL | NULL    |       |
| DEPARTMENT_ID | int(11) | NO   |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+

Now see the index file.

mysql> SHOW INDEXES FROM job_history;
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| job_history |          0 | PRIMARY     |            1 | EMPLOYEE_ID | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| job_history |          1 | indx_job_id |            1 | JOB_ID      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Explanation:

Here's a brief explanation of the above MySQL code:

• ALTER TABLE job_history: This part of the statement indicates that we want to change the structure of the 'job_history' table.
• ADD INDEX indx_job_id(job_id);: It adds an index named 'indx_job_id' to the 'job_id' column in the 'job_history' table. An index is a data structure that improves data retrieval speed on a database table. In this case, the index is created on the 'job_id' column. This can enhance the performance of queries that involve searching or sorting based on the 'job_id' column.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to drop the existing foreign key fk_job_id from job_history table on job_id column which is referencing to the job_id of jobs table.
Next: Write a SQL statement to drop the index indx_job_id from job_history table.