Views

A view is a virtual table derived from the result set of a query on one or more base tables. It contains no data of its own—only the definition of the query used to generate it, which is stored in the data dictionary. Once created, a view can be queried like a regular table.

Purpose and Benefits

Views simplify complex queries by encapsulating joins, filters, or calculations. They enhance security by restricting access to specific columns or rows, allowing users to interact with only the data they are permitted to see. Additional advantages include:

• Abstracting underlying schema complexity
• Providing consistent interfaces despite changes to base tables
• Enabling column aliasing for clearer semantics

Creation Syntax

CREATE [OR REPLACE] VIEW view_name [(column_alias, ...)]
AS
  SELECT ...
  [WITH READ ONLY];

Example:

CREATE VIEW Student_Class1_View AS
SELECT s.StudentNo, s.StudentName, g.GradeName, s.BornDate, s.Address
FROM student s
JOIN grade g ON s.GradeID = g.GradeID
WHERE s.GradeID = 1;

To list all views in the current database:

SHOW FULL TABLES WHERE Table_type = 'VIEW';

Rules and Limitations

• View names must be unique within a schema.
• Views can be nested (i.e., defined using other views).
• Sorting (ORDER BY) and grouping (GROUP BY) in view definitions may be overridden by outer queries.
• Indexes and triggers cannot be created on views.
• Views and base tables can coexist in the same query.

Modifying and Dropping Views

Replace an existing view:

CREATE OR REPLACE VIEW Student_Class1_View AS ... ;

Alter a view explicitly:

ALTER VIEW Student_Email_View (Email)
AS SELECT Email FROM student;

Drop a view:

DROP VIEW view_name;

Indexes

An index is a specialized database structure that accelerates data retrieval operations on a table. It functions similarly to a book’s index, enabling rapid location of specific records without scanning the entire table.

How Indexes Work

Indexes organize column values in sorted order and maintain pointers (or direct storage, in the case of clustered indexes) to the corresponding rows. This reduces disk I/O by avoiding full table scans.

Advantages and Drawbacks

Pros:

• Faster SELECT queries, especially with WHERE, JOIN, ORDER BY, or GROUP BY clauses
• Efficient range scans and aggregate operations (MIN, MAX)

Cons:

• Slower INSERT, UPDATE, and DELETE due to index maintenance overhead
• Additional storage consumption
• Diminishing returns with excessive or redundant indexes

Types of Indexes

• Single-column vs. composite (multi-column): Composite indexes follow the leftmost prefix rule—the query must reference the first column to utilize the index.
• Unique: Enforces uniqueness of indexed values.
• Full-text: Optimized for text-based searches.
• Clustered vs. non-clustered: See below.

Index Design Principles

• Prioritize indexing columns frequently used in WHERE, JOIN, ORDER BY, or GROUP BY.
• Prefer unique indexes where applicable.
• Avoid over-indexing; each index adds write overhead.
• Use shorter data types for indexed columns to reduce size.
• Remove unused or obsolete indexes.

Creating Indexes

1. During table creation:

CREATE TABLE users (
  id INT,
  email VARCHAR(100),
  INDEX idx_email (email)
);

2. Using CREATE INDEX:

CREATE INDEX idx_user_email ON users (email(50));

3. Using ALTER TABLE:

ALTER TABLE users ADD INDEX idx_user_id (id);

Composite index example:

CREATE INDEX idx_user_name_status ON users (last_name, status);
-- Only usable if queries filter on `last_name` (first column)

Dropping Indexes

DROP INDEX index_name ON table_name;

When Indexes Are Used

MySQL leverages indexes for:

• Filtering with WHERE clauses
• Joining tables on indexed columnss
• Sorting or grouping when the sort key is indexed
• Computing MIN()/MAX() on indexed columns
• Applying the leftmost prefix of a composite index

Clustered vs. Non-Clustered Indexes

• Clustered Index: The table data is physical stored in the order of the index (typical the primary key). InnoDB uses this by default. Each table has only one clustered index.
• Non-Clustered Index: The index structure is separate from the data rows. Leaf nodes contain pointers to the actual row locations (e.g., MyISAM). Multiple non-clustered indexes are allowed.

InnoDB stores the full row data in the clustered index leaf nodes, eliminating the need for additional lookups ("heap fetches"). Non-clustered indexes in InnoDB store the primary key value in their leaves, requiring a secondary lookup into the clustered index—a process known as a double lookup.