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

ALTER TABLE $TABEL_NAME ADD PRIMARY KEY (column_name);

Create Unique Index

CREATE UNIQUE INDEX index_name ON $TABEL_NAME (column_name);

Create Composite Index

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

Create Partial Index

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

Create Full-text Index

CREATE FULLTEXT INDEX index_name ON $TABEL_NAME (column_name);

---

Creating and Dropping Indexes

Create Index

CREATE INDEX index_name ON $TABEL_NAME (column_name);

Create Index with Algorithm

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

Show Indexes

SHOW INDEX FROM $TABEL_NAME;

Show Create Table (includes indexes)

SHOW CREATE TABLE $TABEL_NAME;

Drop Index

DROP INDEX index_name ON $TABEL_NAME;

Alternative Drop Syntax

ALTER TABLE $TABEL_NAME DROP INDEX index_name;

Check Index Usage

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

---

Performance Considerations

Analyze Table Performance

ANALYZE TABLE $TABEL_NAME;

Check Index Statistics

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

Monitor Index Usage

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

Force Index Usage

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

Ignore Index

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

---

Views

Create View

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

Create or Replace View

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

Select from View

SELECT * FROM view_name;

Show Views

SHOW FULL TABLES WHERE Table_type = 'VIEW';

Drop View

DROP VIEW view_name;

Alter View

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

View Information

SHOW CREATE VIEW view_name;

---

Stored Procedures and Functions

Create Stored Procedure

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

CALL procedure_name(value1, @output_var);

Create Function

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 ;