DEV Community: Robiul Awal

Index Type

Robiul Awal — Mon, 10 Feb 2025 06:46:45 +0000

Primary Index

Use for uniquely identifying rows (primary key).

A primary index is automatically created when you define a primary key in a table.
It enforces uniqueness and ensures that no two rows have the same value in the primary key column(s).
In many databases (e.g., MySQL with InnoDB), the primary index is also a clustered index, meaning it determines the physical order of data in the table.
How it works
The primary index stores the primary key values in a sorted order (e.g., a B-tree structure).
When you query using the primary key, the database can quickly locate the row.
Use it for the column(s) that uniquely identify each row in the table (e.g., id).

Example:
CREATE TABLE employees (
id INT PRIMARY KEY, -- Primary key (and primary index) is created automatically
name VARCHAR(100),
department VARCHAR(50)
);

Query using the primary key
SELECT * FROM employees WHERE id = 101;

Secondary Index

Use for speeding up queries on non-key columns.

A secondary index is any index that is not the primary index.
It is created on non-primary key columns to speed up queries that filter, sort, or join on those columns.
Secondary indexes are typically non-clustered, meaning they store a separate data structure pointing to the actual rows.
The secondary index stores the indexed column values along with a pointer to the corresponding row in the table.
When you query using the indexed column, the database uses the secondary index to locate the rows.
Use it for columns frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.

Example:
CREATE INDEX idx_department ON employees (department); -- Secondary index
SELECT * FROM employees WHERE department = 'Sales';

Unique Index

Use to enforce uniqueness in a column.

A unique index ensures that all values in the indexed column(s) are unique.
It is similar to a primary index but does not have to be the primary key.
The unique index prevents duplicate values from being inserted into the indexed column(s).
It can be created on one or more columns.
Use it to enforce uniqueness in a column (e.g., email, username).

Example: CREATE UNIQUE INDEX idx_email ON employees (email); -- Unique index
Attempting to insert duplicate values will result in an error:

INSERT INTO employees (id, name, email) VALUES (1, 'John', 'john@example.com');
INSERT INTO employees (id, name, email) VALUES (2, 'Jane', 'john@example.com'); -- Error: Duplicate email

Composite Index

Use for queries involving multiple columns.

A composite index is an index created on multiple columns.
The order of columns in the index matters: the leftmost column is used first for searching.
The composite index stores a combination of values from the indexed columns.
It is useful for queries that filter or sort by multiple columns.
Use it when queries frequently filter or sort by multiple columns together.

Example:
CREATE INDEX idx_dept_salary ON employees (department, salary); -- Composite index

Query using the composite index:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

Clustered Index

Use for range queries or sorting (one per table).

A clustered index determines the physical order of data in a table.
A table can have only one clustered index (usually the primary key).
The data rows are stored in the order of the clustered index.
The clustered index stores the actual data rows in the index structure (e.g., a B-tree).
When you query using the clustered index, the database can retrieve the data directly.
Use it for columns frequently used in range queries (e.g., BETWEEN, >, <) or for columns that are often sorted.

Example:

CREATE CLUSTERED INDEX idx_clustered ON employees (id); -- Clustered index

Query using the clustered index:

SELECT * FROM employees WHERE id BETWEEN 100 AND 200;

Full-Text Index

Use for advanced text search in large text fields.

A full-text index is used for efficient searching of text data (e.g., searching for words or phrases in large text fields).
It supports advanced text search features like keyword matching, phrase matching, and relevance ranking.
The full-text index breaks down the text into tokens (words) and stores them in an optimized structure for fast searching.
It is commonly used with MATCH and AGAINST clauses in SQL.
Use it for columns containing large text data (e.g., description, comments).

Example:

CREATE FULLTEXT INDEX idx_description ON products (description); -- Full-text index

Query using the full-text index:

SELECT * FROM products WHERE MATCH(description) AGAINST('database');

When to Use Which Index?

Scenario Recommended Index
Uniquely identifying rows ==> Primary Index
Speeding up queries on non-key columns ==> Secondary Index
Enforcing uniqueness ==> Unique Index
Filtering/sorting by multiple columns ==> Composite Index
Range queries or sorting ==> Clustered Index
Searching text data ==> Full-Text Index

How to install MySql on Linux

Robiul Awal — Fri, 07 Feb 2025 04:54:33 +0000

Step 1: Update Your Package List
sudo apt update
Step 2: Install MySQL Server
sudo apt install mysql-server
Step 3: Start MySQL Service
sudo systemctl start mysql
Step 4: Enable MySQL to Start on Boot
sudo systemctl enable mysql
Step 5: Secure MySQL Installation
Need to run the following command to set up security options like setting a root password, removing anonymous users, and disabling remote root login:

sudo mysql_secure_installation

Types of Database Relationships

Robiul Awal — Thu, 06 Feb 2025 15:46:25 +0000

One-to-One (1:1) Relationship

Definition: A single record in one table is related to only one record in another table, and vice versa.
Example: A User table and a UserProfile table, where each user has only one profile.
Use Case: Used when I want to split a table for security, performance, or organizational reasons.

One-to-One Code Example
Add a foreign key to one of the tables that references the primary key of the other table.

CREATE TABLE User (
user_id INT PRIMARY KEY,
username VARCHAR(50)
);

CREATE TABLE UserProfile (
profile_id INT PRIMARY KEY,
user_id INT UNIQUE,
bio TEXT,
FOREIGN KEY (user_id) REFERENCES User(user_id)
);

One-to-Many (1:N ) Relationship

Definition: A single record in one table can be related to one or more records in another table.
Example: A Customer table and an Orders table, where one customer can place many orders.
Use Case: Most common relationship type, used in scenarios like blog posts and comments, or authors and books.

One to Maney Code Example

Add a foreign key to the "many" side table that references the primary key of the "one" side table.

CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

Many-to-Many (M:N ) Relationship

Definition: Multiple records in one table are related to multiple records in another table.
Example: A Students table and a Courses table, where many students can enroll in many courses.
Implementation: Requires a junction table (also called an associative entity or bridge table) to link the two tables.

Many-to-Many Code Example

Create a junction table with foreign keys referencing the primary keys of both tables.

CREATE TABLE Students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);

CREATE TABLE StudentCourses (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Database data types

Robiul Awal — Wed, 05 Feb 2025 02:48:40 +0000

Database data can be stored in a column of a table. Each database management system (DBMS) like MySQL, PostgreSQL, or SQL Server has its own set of data types, but many are similar across systems. Let’s dive into the most common database data types:

Numeric Data Types (Used for storing numbers)

=> Integer Types

INT or INTEGER: Stores whole numbers (e.g., 5, -10).
TINYINT: A smaller range of integers (e.g., 0 to 255).
SMALLINT: A slightly larger range than TINYINT.
BIGINT: A very large range of integers.

=> Floating-Point Types

FLOAT: Stores approximate decimal numbers (e.g., 3.14).
DOUBLE or REAL: Stores larger or more precise decimal numbers.
DECIMAL(p, s) or NUMERIC(p, s): Stores exact decimal numbers with precision (p) and scale (s). For example, DECIMAL(5, 2) can store 123.45.

String Data Types

=> Fixed-Length Strings

CHAR(n): Stores a fixed-length string of n characters (e.g., CHAR(10) for "Hello____").

=> Variable-Length Strings

VARCHAR(n): Stores a variable-length string up to n characters (e.g., VARCHAR(255) for "Hello").
TEXT: Stores large text data (e.g., paragraphs or documents).

Date and Time Data Types

=> Date

DATE: Stores a date in the format YYYY-MM-DD (e.g., 2023-10-25)

=> Time

TIME: Stores a time in the format HH:MM:SS (e.g., 14:30:00).
=> Date and Time
DATETIME: Stores both date and time (e.g., 2023-10-25 14:30:00).
TIMESTAMP: Similar to DATETIME, but often used for tracking changes (e.g., row updates).

Binary Data Types
Used for storing binary data like images, files, or serialized objects.

BINARY(n): Stores fixed-length binary data
VARBINARY(n): Stores variable-length binary data.
BLOB: Stores large binary objects (e.g., images or files)

Boolean Data Type

BOOLEAN or BOOL: Stores TRUE or FALSE values.

Specialized Data Types

=> Enumerated Types

ENUM: Stores a value from a predefined list (e.g., ENUM('Red', 'Green', 'Blue')).

=> JSON

JSON: Stores JSON-formatted data (e.g., {"name": "Alice", "age": 25}).

=> UUID

UUID: Stores universally unique identifiers (e.g., 123e4567-e89b-12d3-a456-426614174000).

. Examples in SQL
Here’s how you might define a table with different data types in SQL:

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY, -- Integer
FirstName VARCHAR(50), -- Variable-length string
LastName VARCHAR(50), -- Variable-length string
BirthDate DATE, -- Date
Salary DECIMAL(10, 2), -- Decimal number
IsActive BOOLEAN, -- Boolean
ProfilePicture BLOB, -- Binary data
CreatedAt TIMESTAMP -- Date and time
);