# Indexes

## What are Indexes

*Indexes in MySQL are data structures used to improve the speed of data retrieval operations on database tables. They work like an index in a book, helping MySQL quickly locate the desired rows without having to scan the entire table. Indexes consume additional disk space but significantly improve query performance by creating shortcuts to data. They are essential for optimizing database performance, especially for large tables with frequent read operations.*

***

## Types of Indexes

*Create Primary Index*

```sql
ALTER TABLE $TABEL_NAME ADD PRIMARY KEY (column_name);
```

*Create Unique Index*

```sql
CREATE UNIQUE INDEX index_name ON $TABEL_NAME (column_name);
```

*Create Composite Index*

```sql
CREATE INDEX index_name ON $TABEL_NAME (column1, column2);
```

*Create Partial Index*

```sql
CREATE INDEX index_name ON $TABEL_NAME (column_name(10));
```

*Create Full-text Index*

```sql
CREATE FULLTEXT INDEX index_name ON $TABEL_NAME (column_name);
```

***

## Creating and Dropping Indexes

*Create Index*

```sql
CREATE INDEX index_name ON $TABEL_NAME (column_name);
```

*Create Index with Algorithm*

```sql
CREATE INDEX index_name ON $TABEL_NAME (column_name) ALGORITHM=INPLACE;
```

*Show Indexes*

```sql
SHOW INDEX FROM $TABEL_NAME;
```

*Show Create Table (includes indexes)*

```sql
SHOW CREATE TABLE $TABEL_NAME;
```

*Drop Index*

```sql
DROP INDEX index_name ON $TABEL_NAME;
```

*Alternative Drop Syntax*

```sql
ALTER TABLE $TABEL_NAME DROP INDEX index_name;
```

*Check Index Usage*

```sql
EXPLAIN SELECT * FROM $TABEL_NAME WHERE column_name = 'value';
```

***

## Performance Considerations

*Analyze Table Performance*

```sql
ANALYZE TABLE $TABEL_NAME;
```

*Check Index Statistics*

```sql
SELECT * FROM information_schema.statistics 
WHERE $TABEL_NAME = 'your_table_name';
```

*Monitor Index Usage*

```sql
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE object_schema = 'your_database';
```

*Force Index Usage*

```sql
SELECT * FROM $TABEL_NAME FORCE INDEX (index_name) WHERE column_name = 'value';
```

*Ignore Index*

```sql
SELECT * FROM table_name IGNORE INDEX (index_name) WHERE column_name = 'value';
```

***

## Views

*Create View*

```sql
CREATE VIEW view_name AS
SELECT column1, column2
FROM $TABEL_NAME
WHERE condition;
```

*Create or Replace View*

```sql
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM $TABEL_NAME
WHERE condition;
```

*Select from View*

```sql
SELECT * FROM view_name;
```

*Show Views*

```sql
SHOW FULL TABLES WHERE Table_type = 'VIEW';
```

*Drop View*

```sql
DROP VIEW view_name;
```

*Alter View*

```sql
ALTER VIEW view_name AS
SELECT column1, column2, column3
FROM $TABEL_NAME
WHERE condition;
```

*View Information*

```sql
SHOW CREATE VIEW view_name;
```

***

## Stored Procedures and Functions

*Create Stored Procedure*

```sql
DELIMITER //
CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(50))
BEGIN
    -- procedure body
    SELECT column1 INTO param2 FROM $TABEL_NAME WHERE id = param1;
END //
DELIMITER ;
```

*Call Stored Procedure*

```sql
CALL procedure_name(value1, @output_var);
```

*Create Function*

```sql
DELIMITER //
CREATE FUNCTION function_name(param1 INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE result INT;
    SELECT COUNT(*) INTO result FROM $TABEL_NAME WHERE id = param1;
    RETURN result;
END //
DELIMITER ;
```