Query Analysis First
Execution plans and slow query logs read before anything is changed.
Database Development
Database Development Because Most Slow Applications Are Slow Databases
When an application is slow, the cause is usually a query. Missing indexes, a schema that made sense at a thousand rows, or an ORM quietly issuing hundreds of queries per page. Database development is schema design, indexing and query work — the least visible part of a build and frequently the one that decides how it performs.
We Measure Before Optimising
Database optimisation done by intuition usually adds indexes nothing uses and misses the query that matters.
Indexes That Are Used
Every index costs write performance. Unused ones are pure overhead.
Schema for the Real Volume
A design that works at a thousand rows and fails at a million is a design that failed.
ORM Output Inspected
What the framework actually issues, not what the code appears to do.
Migrations Reversible
Schema changes versioned, tested and able to roll back.
Database Work, Explained
Why Do Databases Get Slow?
Four causes, and all four are diagnosable rather than mysterious.
Discuss Your Database →- 1
Missing Indexes
A query scanning an entire table because the column it filters on has no index.
- 2
Too Many Queries
An ORM issuing one query per row in a loop — fine at ten rows, fatal at two thousand.
- 3
Schema Outgrown
A structure designed when the data was small, now requiring joins across millions of rows.
- 4
Over-Indexing
Indexes added for every query, each one slowing every write.
What Database Work Covers
Diagnosis, schema, indexing and the migration path to get from here to there.
Slow pages are often database problems — see website speed optimization.
How We Approach Database Work
Measurement before change. Optimising the wrong query is worse than doing nothing, because it looks like progress.
Measure
Slow query log, execution plans and query counts per page.
Find the Real Cost
The query run ten thousand times matters more than the one that takes two seconds once.
Fix
Indexes, query rewrites and eager loading, one change at a time.
Verify
Re-measured, because index changes do not always help and occasionally hurt.
Plan for Growth
What breaks at ten times the volume, identified before it does.
Diagnosis
Why the Slowest Query Is Rarely the Problem
Total cost is frequency times duration, and the instinct is to look only at duration.
How do you find the expensive query?
By total time rather than individual time. A query taking three seconds and running once an hour costs three seconds an hour. A query taking eight milliseconds and running fifty thousand times costs several minutes.
Slow query logs surface the first kind and frequently miss the second entirely, because each individual execution is below the threshold.
The useful measurement is queries per request multiplied by duration, which is why counting queries on a page is often more informative than reading the slow log.
When does an index hurt?
Every index has to be updated on every insert, update and delete of the indexed column. On a write-heavy table, indexes added liberally slow down the writes that matter.
Unused indexes are the worst case: all of the write cost and none of the read benefit. Most databases can report which indexes are never used, and the answer is usually several.
Indexing is therefore a trade rather than an improvement, and it should follow from measured query patterns rather than from adding one to every column that appears in a WHERE clause.
Questions,answered.
Database development covers schema design, indexing, query optimisation and migrations — the structure and access patterns that determine how an application performs as its data grows.
Most often the database: a missing index, a query issued once per row in a loop, or a schema that no longer suits the data volume. Measuring queries per request usually identifies it quickly.
Only ones that measured queries will actually use. Every index slows writes, and unused indexes carry that cost with no benefit. Most databases can list which indexes are never used.
For most business applications, relational. The data has relationships, you will want to query it in ways not anticipated at design time, and transactional consistency matters. NoSQL suits specific access patterns rather than being a general upgrade.
Through versioned, tested migrations that can roll back, applied the same way in every environment. Changes made by hand in production are how environments drift apart and deployments start failing unpredictably.
Still deciding if database development is right for you?
Talk to UsThe Query Nobody Notices Costs the Most
Database investigations start at the slow query log, which lists the queries exceeding a threshold. It is the obvious place to look and it systematically misses the biggest problem.
A query taking eight milliseconds never appears there. Run fifty times per page load across every page, it is the single largest consumer of database time on the system — and it is invisible to the tool everyone reaches for first.
Counting queries per request finds it in minutes. It is a less sophisticated measurement than an execution plan and it is more often the one that matters.
Free Database Review
Find Out What Your Database Is Spending Time On
Tell us about the application and where it feels slow. We will look at query counts, execution plans and index usage, and tell you what is actually costing time.
Claim Your Free Marketing Audit
Enhance Your Brand Potential At No Cost!
- Expect a response within 24 hours
- NDA available upon request
- Dedicated product specialists