SQL Query Optimization Tips – Improve Performance and Speed

Introduction

In SQL, text data types are used to store alphanumeric values like names, addresses, emails, and descriptions. Choosing the correct text type — CHAR, VARCHAR, or TEXT — is important for optimizing storage space, query speed, and database performance.

In this section, you'll learn the definitions, differences, and best use cases for each text data type.

1. CHAR (Fixed-Length String)

CHAR is used to store fixed-length strings. If the stored string is shorter than the defined length, SQL automatically pads it with spaces to match the specified size.

Features:

• Fixed length
• Fast and predictable performance
• Uses extra storage if the data is often shorter than the specified length

Syntax:

column_name CHAR(length);

length = number of characters (1 to 255 depending on the database system)

Example:

CREATE TABLE countries (
country_code CHAR(2),
country_name CHAR(50)
    );

country_code like 'US', 'IN', 'UK' will always take 2 characters.

When to Use CHAR:

• Data with a constant size, such as country codes, gender ('M', 'F'), state abbreviations
• Fixed-format fields like credit card types ('VISA', 'MC')
• When exact storage size is known and consistent

2. VARCHAR (Variable-Length String)

VARCHAR stands for Variable Character. It stores variable-length strings, meaning only the actual characters are stored without unnecessary padding.

Features:

• Variable length
• More space-efficient than CHAR for varying-length text
• Slightly slower than CHAR when processing large volumes (because of extra calculations for string lengths)

Syntax:

column_name VARCHAR(length);

length = maximum number of characters allowed

Example:

CREATE TABLE employees (
first_name VARCHAR(50),
email VARCHAR(100)
    );

Names and emails can vary in length, making VARCHAR ideal.

When to Use VARCHAR:

• Data with unpredictable or variable length
• Names, emails, addresses, and descriptions under 255-65535 characters
• Most general-purpose text fields

3. TEXT (Large Text Field)

TEXT is used to store large amounts of text like long descriptions, blog posts, comments, or articles.

Features:

• Meant for large text storage (up to 65,535 characters for standard TEXT in MySQL)
• Cannot have a default value (in some databases like MySQL)
• TEXT fields are stored outside the main table with a pointer reference
• Different variants exist (TINYTEXT, MEDIUMTEXT, LONGTEXT) for various sizes

Syntax:

column_name TEXT;

Example:

CREATE TABLE articles (
id INT,
title VARCHAR(255),
body TEXT
   );

body will store the full article content, which can be very large.

When to Use TEXT:

• Long-form text fields (comments, articles, reviews, reports)
• Data that exceeds normal VARCHAR limits
• When exact storage requirements are unknown or potentially very large

Quick Comparison: CHAR vs VARCHAR vs TEXT

Important Tips

• Use CHAR only when all values will be exactly the same length
• VARCHAR is the best choice for most standard text fields
• Reserve TEXT for content that exceeds VARCHAR limits
• Consider VARCHAR(MAX) in SQL Server for large text that might need indexing
• Be aware that TEXT fields may have limitations on default values and full-text indexing

1. Use Indexes Wisely

Indexes drastically improve performance for read-heavy operations.

Add indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY.

Use composite indexes when filtering by multiple columns.

Avoid indexing columns with low cardinality (e.g., gender, status).

Use EXPLAIN or EXPLAIN ANALYZE to check if your query is using indexes.

2. Avoid SELECT *

-- Bad
SELECT * FROM employees;

-- Good
SELECT id, name, department FROM employees;

Why: SELECT * fetches unnecessary columns, increases network load, and slows down performance.

3. Filter Early with WHERE Clause

Always filter rows as early as possible in your query:

SELECT name FROM employees WHERE status = 'active';

Why: Reduces the amount of data processed and returned.

4. Use Joins Properly

Prefer INNER JOIN over OUTER JOIN unless null values are required.

Use indexed columns in JOIN conditions.

Avoid unnecessary joins or complex subqueries if not needed.

5. Use EXISTS Instead of IN for Subqueries

-- Slower on large data sets
SELECT name FROM employees WHERE id IN (SELECT employee_id FROM attendance);

-- Faster
SELECT name FROM employees WHERE EXISTS (
  SELECT 1 FROM attendance WHERE attendance.employee_id = employees.id
);

EXISTS is often more efficient because it short-circuits after finding the first match.

6. Use LIMIT to Control Output

SELECT * FROM products LIMIT 100;

Why: Prevents overloading memory and improves load time, especially in web apps.

7. Use Aggregate Functions Efficiently

Avoid using COUNT(*) on large tables unless necessary. Instead, filter rows first or use indexed columns.

-- Inefficient
SELECT COUNT(*) FROM orders;

-- More efficient
SELECT COUNT(id) FROM orders WHERE status = 'completed';

8. Normalize Your Database (But Not Over-Normalize)

Use proper normalization to eliminate redundancy.

Use foreign keys and constraints to ensure data integrity.

Don't split data into too many tables — it may lead to join-heavy queries.

9. Use Query Execution Plans

Use tools like:

• EXPLAIN (MySQL, PostgreSQL)
• EXPLAIN PLAN (Oracle)
• SET SHOWPLAN_ALL ON (SQL Server)

These show how your query will execute — you can identify full table scans, index usage, and bottlenecks.

10. Benchmark and Profile Queries

Test queries on real data sets.

Use tools like MySQL Workbench, pgAdmin, or SQL Server Management Studio Profiler to analyze query cost.