Database Optimization: 11 Ways To Make Your Database Faster

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/21

flashcard set

Earn XP

Description and Tags

Database optimization is the process of making a database answer queries faster, use less CPU, memory and disk, and handle more users without slowing down. A slow database is usually not a hardware problem first. It is a query problem, an index problem, or a design problem. Optimization means finding the slow queries, fixing how they run, and checking the result with measurements — not guessing. Full guide: https://denebrixai.com/blog/database-optimization/

22 Terms

1
New cards

Slow query log

Rank queries by total time and fix the top few first. In PostgreSQL use pg_stat_statements; in MySQL use the slow query log; in SQL Server use wait statistics and DMVs.

2
New cards

EXPLAIN ANALYZE

Run EXPLAIN, and EXPLAIN ANALYZE for read queries, to see the real plan and catch full table scans on large tables. EXPLAIN ANALYZE actually runs the query, so do not run it on a write query in production without care.

3
New cards

Indexing

Add indexes on columns used in WHERE, JOIN, GROUP BY and ORDER BY. Avoid functions on indexed columns in WHERE, because that can stop the index being used. Do not over-index — every extra index slows INSERT and UPDATE.

4
New cards

SELECT only what you need

Avoid SELECT *. Select only the columns the query needs. This cuts I/O, memory and network time, especially on wide tables.

5
New cards

Query rewrite

Rewrite OR conditions, correlated subqueries and unnecessary JOINs where a simpler form does the same work. Test each rewrite with EXPLAIN.

6
New cards

Caching

If the same query runs again and again, cache the result. Caching cuts read load far more than tuning alone when reads repeat.

7
New cards

Partitioning

Partition large tables by date or range when reads are mostly for one period. MySQL and PostgreSQL both support partitioning, and it lets the database scan one partition instead of the whole table.

8
New cards

Buffer pool / memory

Size the buffer pool and cache so hot indexes and data fit in RAM. In MySQL that is innodb_buffer_pool_size; in PostgreSQL it is shared_buffers and effective_cache_size.

9
New cards

Connection pooling

Opening a new connection for every query adds overhead. Use a pooler such as PgBouncer for PostgreSQL, or your driver's pool.

10
New cards

ANALYZE and VACUUM

Run ANALYZE regularly so the planner has fresh statistics. Remove unused indexes, and vacuum / maintain tables in PostgreSQL so bloat does not grow.

11
New cards

Measure after every change

Measure before and after a change: query time, rows scanned, buffer hits, CPU and I/O. If a change does not improve the measured number, revert it.

12
New cards

N+1 query problem

Running one query to get a list, then one extra query per item in a loop — 101 queries instead of 1. Fix it with a single smart JOIN.

13
New cards

Full table scan

The database reads every row of a table to answer a query. On a large table this is the usual cause of a slow query — check it with EXPLAIN.

14
New cards

B-Tree index

The default index type in most databases. Like an index at the back of a textbook, it lets the database jump straight to the right rows instead of scanning all of them.

15
New cards

Covering index

An index that contains all the columns a query needs, so the database never has to read the table itself.

16
New cards

Keyset pagination

Pagination with WHERE id > last_id instead of OFFSET. Page 5,000 loads as fast as page 1, because the database does not skip rows one by one.

17
New cards

OFFSET pagination

Pagination with LIMIT + OFFSET. It gets slower as the page number grows, because the database still scans and discards all the earlier rows.

18
New cards

PgBouncer

A connection pooler for PostgreSQL. It reuses a small pool of connections instead of opening a new one for every query.

19
New cards

Materialized view

A pre-computed result stored like a table. Expensive queries run once, and later reads use the stored result instead of re-computing it.

20
New cards

Buffer hit

A read served from memory (cache) instead of disk. More buffer hits and fewer disk reads mean faster queries — check the buffer hit ratio after a change.

21
New cards

MySQL — what to check

InnoDB buffer pool (innodb_buffer_pool_size), slow query log, partitioning by range.

22
New cards

PostgreSQL vs SQL Server — what to check

PostgreSQL: EXPLAIN ANALYZE, pg_stat_statements, ANALYZE, VACUUM, PgBouncer. SQL Server: wait statistics, DMVs, clustered vs non-clustered indexes, columnstore for analytics.