MariaDB Alter Table Add Index

This MariaDB tutorial will discuss the execution of the MariaDB Alter Table Add Index statement. Here we will discuss and draw lessons from several cases to help you comprehend the subject better. The whole list of subjects we’ll discuss is provided below.

  • MariaDB Alter Table Add Index
  • MariaDB Alter Table Add Index Example
  • MariaDB Alter Table Add Unique Index
  • MariaDB Alter Table Add Index as Column_Names
  • MariaDB Alter Table Add Index Drop
  • MariaDB Alter Table Add Index If Not Exists
  • MariaDB Alter Table Add Index Json Column
  • MariaDB Alter Table Add Index Multiple Columns
  • MariaDB Alter Table Add Index on temporary
  • MariaDB Alter Table Add Index Primary Key Example
  • MariaDB Alter Table Add Index Syntax Error

MariaDB Alter Table Add Index

Here, we will learn and understand how to add an index to the table by using the MariaDB ALTER TABLE statement by the query, which will be explained with the help of syntax and an illustrated example.

The MariaDB CREATE INDEX statement is used to create an index that will improve the speed of the queries. The database index is similar to a book index which is normally used to find information quicker and easier.

Here is the syntax of the MariaDB ALTER TABLE statement to add an index on the column of the table by the following query:

SYNTAX:

ALTER TABLE ADD INDEX INDEX_NAME (COLUMN_NAME);

EXAMPLE:

ALTER TABLE STATES_OF_USA ADD INDEX STATENAME(STATE_NAME);

EXPLAIN SELECT * FROM STATES_OF_USA
WHERE STATE_NAME LIKE '%a';

In the first query, we have used the ALTER TABLE statement is used to add the index as STATENAME on the STATE_NAME column of the STATES_OF_USA table.

And with the help of the EXPLAIN SELECT statement, the number of rows that the query optimizer has specified in the ROW column is the same as the number of rows in the STATE_NAME column of the STATES_OF_USA table.

MariaDB alter table add index
Example of MariaDB Alter Table Add Index

We hope that you have understood the subtopic “MariaDB Alter Table Add Index” by using the MariaDB ALTER TABLE statement on the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: MariaDB Add Auto Increment Column

MariaDB Alter Table Add Index Example

Here we will learn and understand how to add an index on the column of the table by using the MariaDB ALTER TABLE statement by the following query:

EXAMPLE:

ALTER TABLE STATES_OF_USA ADD INDEX STATE_ABBREVIATION(STATE_SHORTFORM);

EXPLAIN SELECT * FROM STATES_OF_USA
WHERE STATE_SHORTFORM LIKE '%I%';

As we see in the above query, we have used the ALTER TABLE statement to add an index as STATE_ABBREVIATION on the STATE_SHORTFORM column of the STATES_OF_USA. Then we used the EXPLAIN SELECT statement to retrieve all records from the STATES_OF_USA table based on the WHERE condition.

This means that the number of rows shown in the ROW column is the same as the total number of rows in the STATE_SHORTFORM column of the STATES_OF_USA table.

MariaDB alter table add index example
MariaDB Alter Table Add Index Example

We hope that you have understood how to add an index to the column of the table by using the MariaDB ALTER TABLE statement by the query. We have used an example and explained it in depth, for better understanding.

Read: MariaDB Str_To_Date

MariaDB Alter Table Add Unique Index

We will learn and understand how to add the MariaDB UNIQUE INDEX to the column of the table by using the MariaDB ALTER TABLE statement on the following query:

EXAMPLE:

ALTER TABLE JOHNS_HOPKINS_HOSPITAL ADD UNIQUE INDEX FIRSTNAME (PATIENT_FIRSTNAME);

EXPLAIN SELECT DISTINCT PATIENT_FIRSTNAME FROM JOHNS_HOPKINS_HOSPITAL;

In the first query, the ALTER TABLE statement is used to add a unique index as FIRSTNAME on the PATIENT_FIRSTNAME column of the JOHNS_HOPKINS_HOSPITAL table. Then we used the EXPLAIN SELECT statement with the DISTINCT clause on the PATIENT_FIRSTNAME column which help to find a unique string_name from the JOHNS_HOPKINS_HOSPITAL table.

With this, in the result set, it will get all names and shows 43 rows in the ROWS column which is the same as the DISTINCT clause used on the PATIENT_FIRSTNAME column in the JOHNS_HOPKINS_HOSPITAL table.

MariaDB alter table add unique index example
Example of MariaDB Alter Table Add Unique Index

We hope that you have understood the subtopic “MariaDB Alter Table Add Unique Index” by using the MariaDB ALTER TABLE statement on the table by the query. For a better definition, we have used an example and demonstrated it in depth.

Read: MariaDB Unique Key

MariaDB Alter Table Add Index as Column_Names

In this MariaDB subtopic tutorial, we will learn and understand how to add an index for the column and give an alias name for it on the table by the following query:

EXAMPLE:

ALTER TABLE MIT_STUDENTS ADD INDEX COLUMN_NAMES 
(FIRST_NAME);

EXPLAIN SELECT FIRST_NAME FROM MIT_STUDENTS;

As we see in the above query, we have used the ALTER TABLE statement to add an index called COLUMN_NAMES on the FIRST_NAME column of the MIT_STUDENTS table. A new index that has been made on the FIRST_NAME column will be shown inside the KEY column of the INDEX book.

And the total number of rows inside the ROWS column is the same as the number of rows inside the FIRST_NAME column of the MIT_STUDENTS table which is 20.

Next, we used the EXPLAIN SELECT statement to retrieve all records of the FIRST_NAME column in the MIT_STUDENTS table and show all related information for the index which is made on the FIRST_NAME column which is shown inside the INDEX book as the result set.

MariaDB alter table add index as column_names example
Example of MariaDB Alter Table Add Index As Column_Names

We hope that you have understood the subtopic “MariaDB Alter Table Add Index as Column_Names” by using the MariaDB ALTER TABLE and EXPLAIN SELECT statement on the table by the query. For better familiarity, we have used an example and clarified it in deepness.

Read: MariaDB Difference Between Two Dates

MariaDB Alter Table Add Index Drop

Here we will learn and understand how to drop the existing index on the column of the table by the following query:

SYNTAX:

ALTER TABLE YOUR_TABLE_NAME DROP INDEX INDEX_NAME;

EXAMPLE:

ALTER TABLE STATES_OF_USA DROP INDEX STATE_ABBREVIATION; 

SHOW INDEXES FROM STATES_OF_USA;

In the above query, we have used the ALTER TABLE statement to drop an index called STATE_ABBREVIATION from the STATES_OF_USA table. If we want to check index has been removed from the STATES_OF_USA table then we will use the SHOW INDEXES statement.

MariaDB alter table add index drop example
Example of MariaDB Alter Table Add Index Drop Statement

We hope that you have understood the subtopic “MariaDB Alter Table Add Index Drop” by using the MariaDB ALTER TABLE statement on the table by the query. For a better definition, we have used an example and described it in deepness.

Read: MariaDB Date Greater Than 

MariaDB Alter Table Add Index If Not Exists

In this MariaDB section, we will learn and understand how to add an index if not exists on the column of the table by the following query:

EXAMPLE:

ALTER TABLE JOHNS_HOPKINS_HOSPITAL ADD INDEX IF NOT EXISTS LASTNAME (PATIENT_LASTNAME);

EXPLAIN SELECT * FROM JOHNS_HOPKINS_HOSPITAL
WHERE PATIENT_LASTNAME LIKE '%e%'; 

In the preceding query, we have used the ALTER TABLE statement to add the index as LASTNAME on the PATIENT_LASTNAME column of the JOHNS_HOPKINS_HOSPITAL table. And the IF NOT EXISTS clause is used to check the index name as LASTNAME already exists in the INDEX book or not. But if it does not exist, it will prevent it from error.

In the second query, we have used the EXPLAIN SELECT statement to retrieve all records from the JOHNS_HOPKINS_HOSPITAL table with the WHERE condition. In the WHERE condition, the PATIENT_LASTNAME column is used with the LIKE operator to find the name ends in between from the JONHS_HOPKINS_HOSPITAL table.

In the output result set, it will provide the total number of rows as 43 in the ROWS column of the INDEX book.

MariaDB alter table add index if not exists example
Example of MariaDB Alter Table Add Index If Not Exists

We hope that you have understood the subtopic “MariaDB Alter Table Add Index If Not Exists” by using the MariaDB ALTER TABLE IF NOT EXISTS statement on the table by the query. We have used an example and explained it in depth for a better understanding.

Read: MariaDB Check Empty String

MariaDB Alter Table Add Index Json Column

Here we will learn and understand how to use the MariaDB ALTER TABLE statement to add an index on the JSON column of the table by the following query:

EXAMPLE:

ALTER TABLE JSON_DEMO ADD INDEX JSON_DATATYPE (JSON_VALUE);

EXPLAIN SELECT JSON_VALUE FROM JSON_DEMO;

In the aforementioned query, the ALTER TABLE statement is used to add an index called JSON_DATATYPE on the JSON_VALUE column of the JSON_DEMO table. Then we used the EXPLAIN SELECT statement to retrieve all records of the JSON_VALUE column from the JSON_DEMO table.

This means that the number of rows in the JSON_VALUE column is 6 rows which is the same as the number of rows in the ROWS column of the INDEX book.

MariaDB alter table add index json column example
Example of MariaDB Alter Table Add Index Json Column

We hope that you have understood the subtopic “MariaDB Alter Table Add Index Json Column” by using the MariaDB ALTER TABLE statement on the column of the table by the query. We have used an example and described it in depth, for better learning.

Read: MariaDB Check String Length

MariaDB Alter Table Add Index Multiple Columns

In this MariaDB subtopic section, we will learn and understand how to add the index on the multiple columns of the table by the following query:

EXAMPLE:

ALTER TABLE JOHNS_HOPKINS_HOSPITAL ADD INDEX PATIENT_INDEXES (PATIENT_EMAIL, GENDER);

EXPLAIN SELECT PATIENT_EMAIL, GENDER FROM JOHNS_HOPKINS_HOSPITAL;

In the query above, the ALTER TABLE statement is used to add index as PATIENT_INDEXES on both columns i.e; PATIENT_EMAIL and GENDER columns from the JOHNS_HOPKINS_HOSPITAL table.

Then we used the EXPLAIN SELECT statement to retrieve all records of the PATIENT_EMAIL and GENDER columns from the JOHNS_HOPKINS_HOSPITAL table.

This means it will bring and show the total number of rows as 43 in the ROWS column which is the same as the total number of rows in the PATIENT_EMAIL and GENDER columns from the JOHNS_HOPKINS_HOSPITAL table.

MariaDB alter table add index multiple columns example
Example of MariaDB Alter Table Add Index Multiple Columns

We hope that you have understood the subtopic “MariaDB Alter Table Add Index Multiple Columns” by using the MariaDB ALTER TABLE statement on the table by the query. We have used a model and clarified it in profundity, for better knowledge.

Read: MariaDB Check Constraint + Examples

MariaDB Alter Table Add Index on Temporary Table

In this MariaDB subtopic section, we will learn and understand how to add an index to the temporary table by using the MariaDB ALTER TABLE statement by the following query:

EXAMPLE:

CREATE TEMPORARY TABLE TEMP_USASTATES
(SELECT * FROM states_of_usa);

ALTER TABLE TEMP_USASTATES ADD INDEX USA_ID (STATE_ID);

EXPLAIN SELECT STATE_ID FROM TEMP_USASTATES;

In the preceding query, we have used the CREATE TEMPORARY TABLE statement to create a temporary table called TEMP_USASTATES on the STATES_OF_USA table which will retrieve all records of all columns by using the SELECT statement. Then we used the ALTER TABLE statement to add an index called USA_ID on the STATE_ID column of the TEMP_USASTATES temporary table.

Then we used the EXPLAIN SELECT statement to retrieve all records of the STATE_ID column from the TEMP_USASTATES temporary table. This means that the number of records that are inside the STATE_ID column is the same as the number of rows inside the ROWS column of the INDEX book and the total number of rows is 50.

MariaDB alter table add index on temporary table example
Example of MariaDB Alter Table Add Index on Temporary Table

We hope that you have understood the subtopic “MariaDB Alter Table Add Index on Temporary Table “ by using the MariaDB ALTER TABLE statement on the temporary table by the query. For a better acquaintance, we have used an example and described it in depth.

Read: MariaDB Greatest Function

MariaDB Alter Table Add Index Primary Key Example

Here we will learn and understand how to add an index to the PRIMARY KEY column of the table by the following query:

EXAMPLE:

ALTER TABLE JOHNS_HOPKINS_HOSPITAL ADD INDEX ID_INDEX (PATIENT_ID);

EXPLAIN SELECT PATIENT_ID FROM JOHNS_HOPKINS_HOSPITAL;

In the above query, the ALTER TABLE statement is used to add an index called ID_INDEX on the PATIENT_ID column of the JOHNS_HOPKINS_HOSPITAL table. After that, we used the EXPLAIN SELECT statement to retrieve the total number of rows existing in the PATIENT_ID column as same in the ROW column of the INDEX book.

But if we want to check how many indexes we have created on the columns of the JOHNS_HOPKINS_HOSPITAL table then we will use the EXPLAIN SELECT statement.

MariaDB alter table add index primary key example
MariaDB Alter Table Add Index Primary Key Example

We hope that you have understood the subtopic “MariaDB Alter Table Add Index Primary Key Example” by using the MariaDB ALTER TABLE statement on the table by the query. We have used an example and explained it in depth for a better understanding.

Read: MariaDB Median

MariaDB Alter Table Add Index Syntax Error

Here we will learn and understand how syntax errors are raised while adding index by using the MariaDB ALTER TABLE statement on the table by the following query:

SYNTAX ERROR EXAMPLE:

ALTER TABLE STATES_OF_USA ADD INDEX MULTIPLE_INDEX (STATE_ID), 
 INDEX POPULATION(STATE_POPULATION);

In the above query, we have tried to create an index called MULTIPLE_INDEX and POPULATION on both columns i.e; STATE_ID and STATE_POPULATION columns of the STATES_OF_USA table but it shows a semantic syntax error to create an index on the column of the table.

As the error says, “SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘POPULATION(STATE_POPULATION)’ at line 2”.

MariaDB alter table add index syntax error
Example of MariaDB Alter Table Add Index Syntax Error

ERROR REMOVED EXAMPLE:

ALTER TABLE STATES_OF_USA ADD INDEX MULTIPLE_INDEX (STATE_ID), 
ADD INDEX POPULATION(STATE_POPULATION);

EXPLAIN SELECT STATE_ID, STATE_POPULATION FROM STATES_OF_USA;

Now, we didn’t add the ADD keyword in the first query second line. So, we rewrite the query as the ALTER TABLE statement to add the index called MULTIPLE_INDEX and INDEX_POPULATION on both columns i.e; STATE_ID and STATE_POPULATION columns of the STATES_OF_USA table.

Then we used the EXPLAIN SELECT statement to retrieve all records of the STATE_ID and STATE_POPULATION columns from the STATES_OF_USA table. If we want to have more clarification about adding an index to the table then see the first subtopic “MariaDB Alter Table Add Index”.

MariaDB alter table add index syntax error example
Example of MariaDB Alter Table Add Index Without Syntax Error

We hope that you have understood the subtopic “MariaDB Alter Table Add Index Syntax Error” by using the MariaDB ALTER TABLE statement on the table by the query. For a better description, we have used an illustration and demonstrated it in deepness.

You may also like to read the following MariaDB tutorials.

By the end of this MariaDB tutorial to might understand the use of MariaDB Alter Table Add Index statements after reading this lesson. We also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

  • MariaDB Alter Table Add Index
  • MariaDB Alter Table Add Index Example
  • MariaDB Alter Table Add Unique Index
  • MariaDB Alter Table Add Index as Column_Names
  • MariaDB Alter Table Add Index Drop
  • MariaDB Alter Table Add Index If Not Exists
  • MariaDB Alter Table Add Index Json Column
  • MariaDB Alter Table Add Index Multiple Columns
  • MariaDB Alter Table Add Index on temporary
  • MariaDB Alter Table Add Index Primary Key Example
  • MariaDB Alter Table Add Index Syntax Error