Uppsats

Database Schema Design and Query Response Time : A Back-end Performance Evaluation of Schema Design and Indexing Strategies

Kandidat-uppsats

Alladin, Elias, Gunnarsson, Erik, Wickström, Felicia, Kölerud, Gustav, Pålsson, Jonathan, Aldgård Lindblom, Linn, Carlsson, William

Linköpings universitet/Institutionen för datavetenskap

Publicerad: 2026

Språk: Engelska

Nyckelord

klicka för att söka

⌕Performance Evaluation ⌕Prestandautvärdering ⌕database performance ⌕databasprestanda ⌕MariaDB ⌕SQLite ⌕Digital marketplace ⌕database schema design ⌕query response time ⌕indexing strategies ⌕workload-specific indexes ⌕composite indexes ⌕BCNF normalization ⌕normalized schema ⌕denormalized schema ⌕relational databases ⌕database management systems ⌕SQL queries ⌕query optimization ⌕query execution plan ⌕query processing ⌕read operations ⌕write operations ⌕SELECT queries ⌕INSERT operations ⌕UPDATE operations ⌕back-end performance ⌕database benchmarking ⌕response time measurement ⌕median response time ⌕bootstrap confidence intervals ⌕warm-cache workload ⌕single-user workload ⌕data scalability ⌕dataset size ⌕large-scale databases ⌕indexing overhead ⌕storage overhead ⌕read scalability ⌕update efficiency ⌕data redundancy ⌕schema normalization ⌕database architecture ⌕embedded database ⌕client-server database ⌕B-tree indexes ⌕foreign keys ⌕primary keys ⌕functional dependencies ⌕course literature marketplace ⌕application performance ⌕DBMS-specific behavior ⌕Databasschemadesign ⌕svarstid för databasfrågor ⌕indexeringsstrategier ⌕arbetsbelastningsspecifika index ⌕sammansatta index ⌕BCNF-normalisering ⌕normaliserat schema ⌕denormaliserat schema ⌕relationsdatabaser ⌕Databashanteringssystem ⌕SQL-frågor ⌕frågeoptimering ⌕exekveringsplan för frågor ⌕frågebearbetning ⌕läsoperationer ⌕skrivoperationer ⌕SELECT-frågor ⌕INSERT-operationer ⌕UPDATE-operationer ⌕back-end-prestanda ⌕databasbenchmarking ⌕mätning av svarstid ⌕median svarstid ⌕bootstrap-konfidensintervall ⌕warm-cache-arbetsbelastning ⌕single-user-arbetsbelastning ⌕dataskalbarhet ⌕datamängdsstorlek ⌕storskaliga databaser ⌕indexeringskostnad ⌕lagringskostnad ⌕lässkalbarhet ⌕uppdateringseffektivitet ⌕dataredundans ⌕schemanormalisering ⌕databasarkitektur ⌕inbyggd databas ⌕klient-server-databas ⌕B-trädsindex ⌕främmande nycklar ⌕primärnycklar ⌕funktionella beroenden ⌕kurslitteraturmarknadsplats ⌕digital marknadsplats ⌕applikationsprestanda ⌕DBMS-specifikt beteende

Sammanfattning

This thesis investigated how database schema design and indexing strategies affect query response time. BCNF-normalized and denormalized relational schemas were implemented in SQLite and MariaDB, both with and without workload-specific composite indexes. The evaluation was conducted in the context of a fictional digital marketplace for course literature and measured six predefined operations: browsing listings by course, searching by ISBN, retrieving listings by seller, retrieving a single listing by ID, inserting listings, and updating book metadata. Experiments were performed on datasets containing 100,000, 1,000,000, and 10,000,000 listings, using median query response time as the primary metric. The results show that query response time depends more on how well schemas and indexes match the workload than on normalization or denormalization alone. Workload-specific composite indexes improved read scalability, while normalized schemas handled metadata updates more efficiently and reduced redundancy. Under the evaluated single-user, warm-cache workload, the strongest overall configuration was a normalized schema with workload-specific indexes. Overall, the findings indicate that effective database design requires balancing read performance, update efficiency, redundancy, indexing overhead, and DBMS-specific behavior.