Author Archives: Stanislav_Panteleev

A B-tree index (short for Balanced Tree) is the most common type of index used in relational databases (e.g., PostgreSQL, MySQL InnoDB, Oracle). It organizes data in a way that allows fast lookup, insertion, deletion, and range queries, all in … Continue reading →

🔹 What Is a Composite Index? A composite index is an index on multiple columns, like: This index helps queries that filter or sort using email, email AND status, but not just status alone. 🧠 When Composite Indexes Are Better … Continue reading →

To optimize a slow SQL query, follow a structured approach. Here’s a step-by-step guide with techniques, tools, and tips: 🔍 1. Analyze the Query ✅ Use EXPLAIN (or EXPLAIN ANALYZE) In PostgreSQL: In MySQL: This reveals how the query is … Continue reading →

🐘 PostgreSQL 📌 1. Create a Table ✅ Clustered Index in PostgreSQL PostgreSQL does not have a true clustered index like SQL Server or MySQL’s InnoDB. But you can physically reorder the table using the CLUSTER command on an index: … Continue reading →

🔹 Clustered Index Feature Description Data Storage Physically reorders the table’s rows to match the index. The index is the table. Only One A table can have only one clustered index because data rows can be sorted in only one … Continue reading →

What is SQL Injection? SQL Injection happens when untrusted input (like user input) is mixed into a SQL query without proper handling — allowing an attacker to inject malicious SQL code. 🧨 This can lead to: Data leaks (stealing confidential … Continue reading →

Difference Between UNION and UNION ALL Feature UNION UNION ALL Duplicates Removes duplicate rows (DISTINCT). Keeps all rows, including duplicates. Performance Slower — must sort and remove duplicates. Faster — no extra work to remove duplicates. Result Size Smaller or … Continue reading →

What are Stored Procedures in SQL? ✅ A Stored Procedure is: A saved collection of SQL statements (plus optional logic like loops, conditions) that you can execute later by calling its name. Think of it like a function in programming … Continue reading →

Difference Between Regular Subquery and Correlated Subquery Feature Regular (Non-Correlated) Subquery Correlated Subquery Depends on outer query? ❌ No — runs independently. ✅ Yes — refers to columns from the outer query. Execution Executed once. Executed once for each row … Continue reading →

What is a Subquery in SQL? ✅ A subquery is: A query inside another query. It’s enclosed in parentheses (…). It is executed first, and its result is used by the outer query. It can return: A single value (scalar). … Continue reading →