 |
Editing indexes is part of table editing | |
|
|---|
 |
Module for editing the indexes of a MySQL table:
|
| Indexes can be edited in list or single form. |
| Index creation options can be selected. |
|
 |
Create a new index. |
 |
View index build options. |
|
| Index level functions: |
 |
Table column selection sorted by position in table. |
 |
Table column selection sorted alphabetically. |
 |
Table column selection sorted by data type. |
|
 |
Reload index. |
 |
Start editing form for index. |
 |
Add table column to index at position. |
 |
Delete table column from index. |
 |
Move table column up. |
 |
Move table column down. |
|
| SQL preview: | Show SQL preview for index creation. |
| Rebuild index: | Index is rewritten to the table. |
| Save changes: | Index is saved with changes. |
| Rename index: | Index is renamed. |
| Save as new index: | Index is saved as a new index when the name is changed. |
| Delete index: | Index will be deleted from MySQL server. |
| Restore: | Deleted index is restored. |
|
| Editing and creating form for indices: |
 |
Form for creating and editing indexes in MySQL tables:
|
| Columns can be sorted by pos, field and type. |
| Columns can be filtered by data type. |
|
 |
Add table column to index. |
|
MySQL and MariaDB uses indices that allow for faster data retrieval, thereby increasing the performance of database queries improve. An index is a data structure that organizes the values of one or more columns in a table, allowing for efficient searching and retrieval of data based on those columns. They reduce the number of rows to examine.
MySQL and MariaDB support different types of indexes: B-tree indexes, hash indexes, full-text -Indexes and spatial indexes. The most commonly used index type is the B-tree index, which makes sense in most situations.
A primary key (PRIMARY KEY) in MySQL and MariaDB is a unique index that identifies each row in a table. It provides quick access to specific rows and enforces uniqueness. A primary key does not allow NULL values.
A unique index (UNIQUE) ensures that the indexed columns contain only unique values. It is similar to a primary key, but allows NULL values. A table can contain multiple unique indexes.
Simple indexes can be created over one or more columns. You can speed up queries that involve conditions on one or more columns.
Indices can be created during a table creation (CREATE TABLE), or with ALTER TABLE to a be added to an existing table. Creating too many indexes on a table can negatively impact performance because indexes take up disk space and affect insert, update, and delete operations, by spending more time updating the indexes as well. It is important to tune the most frequently executed queries on indexes. |
