Two questions from the boss, one answer underneath both
The owner of a cram school group with eight branches and 3,200 enrolled students threw two questions at us last week. "The enrolment form just needs one more field, a second parent phone number. Why is that quoted at three days?" And: "The system flew at launch. Now the front desk waits eight seconds for the student list."
Both answers live in the database, and they sit on one chain. Columns decide what your data looks like. Migrations decide what changing a column costs. Indexes decide whether lookups stay fast as data grows. N+1 decides whether your application treats the database like a sweatshop.
Term 1: Table and Column
In one line: a table is a register with a fixed format, and a column is one heading on that register.
Analogy: an Excel workbook. One worksheet is a table, the top row reading "name, phone, birthday" is the columns, and every row beneath is one record. The difference is that a database column carries an ID card: data type, maximum length, whether it may be blank, whether duplicates are allowed.
Example: this cram school runs four main tables, students, enrolments, payments and attendances. Two years in, the attendance table holds 1.8 million rows. The new second parent phone is one column on students, but it will not stay confined to that one table.
Term 2: Index
In one line: an index is a pre-sorted directory built for a column so the database never reads the whole table.
Analogy: a library card catalogue. Without the cards, a librarian walks every shelf from the first aisle to the last; with cards sorted by title, three seconds gets you the exact shelf. The MySQL 8.4 manual puts it bluntly: without an index, the database starts at the first row and reads the entire table. The PostgreSQL documentation says the same.
Example: "a parent gives a phone number, find the student" took 3.2 seconds with no index and 0.04 seconds after indexing the parent phone column, eighty times faster on the same data and the same server. The cost is disk space plus a slightly slower write, since every insert must also maintain the directory. That is why the official optimisation guide recommends indexing only columns you genuinely search, sort or join on.
Term 3: Migration
In one line: a migration turns every change to your database structure into an ordered, replayable, reversible script.
Analogy: renovating a floor plan, with every change drawn up and filed. You do not just swing a hammer at a wall. You draw, build, inspect and keep the drawing, because the next branch gets built from it and because one day you may need to undo it exactly. The Laravel documentation calls migrations version control for the database.
Example: adding that column means writing a migration script and running it on development, staging and production so all three schemas match, then deciding what the 3,200 existing records hold in it. Only then come the form field, validation, admin list, export templates, the monthly report, the parent app's API payload and the tests. Three days is not the price of a column; it is the price of the seven places it touches.
Term 4: The N+1 query
In one line: N+1 is when code makes dozens of trips to the kitchen for something that should have arrived on one tray.
Analogy: ten diners at a table and a waiter carrying one plate at a time. Eleven round trips. The kitchen has not changed and the food has not changed, but the guests wait. Code does the same: one query fetches fifty students (the "1"), then one query per student fetches the assigned teacher (the "N").
Example: the eight-second list showed fifty rows, each with assigned teacher, active course count and monthly attendance rate. Three relationships, one query each, 151 queries per page load. Fetching them in one batch instead (Laravel calls it eager loading) cut that to 4 queries and dropped Largest Contentful Paint from 8.1 seconds to 1.4, inside Google's 2.5-second "good" LCP threshold. The fix took six hours.
How the four connect (a concept diagram)
Picture this. On the left, four horizontal cards are the four tables; the row of small cells along each card's top edge is the columns, and thin lines between cards are their relationships. Below each card hangs a bookmark tab labelled "index"; only columns wearing a tab get a shortcut at lookup time. Down the far left runs a timeline with slots numbered 001, 002, 003, the migration version sequence. On the right, an application connects to the database by two lines: one thick line labelled "1 batched query", and a bundle of fifty thin threads labelled "N scattered queries". The bundle is N+1.
Indexes treat how fast a single query runs. Fixing N+1 treats how many queries one screen fires. Fix only one side and the page is usually still slow.
What it means for budget, schedule and risk
At a typical Taiwan contracting rate of NT$1,200 to NT$1,800 per hour:
- A display-only column: roughly 3 to 4 hours, NT$4,000 to NT$7,000.
- A column that flows into reports and exports: add the admin list, export templates, monthly report, API payload and three-environment deployment, roughly 12 to 18 hours, NT$15,000 to NT$30,000. That is the "three days".
- Adding a missing set of indexes: roughly 3 to 5 hours, NT$4,000 to NT$9,000, usually the best return on the list.
- Fixing one N+1: roughly 4 to 8 hours, NT$5,000 to NT$14,000, against a server upgrade at NT$36,000 to NT$120,000 a year that may miss the cause.
On risk: with no migration history, an incoming team cannot rebuild your schema, which locks you to your current vendor. With no rollback script, one hour of downtime during enrolment peak costs far more than the two hours originally saved.
Five questions to take to your vendor
- "Which tables, screens and reports does this new column touch? Send me the list, I want to see how three days is calculated."
- "Are schema changes managed with migrations? Can you show me the migration files?"
- "If this release goes wrong, where is the rollback script, how long does it take, and does any data get lost?"
- "How many queries does the student list page fire? Is there an N+1? Give me counts and seconds, before and after."
- "What are the three slowest queries now, which indexes are missing, and how much will adding them slow writes?"
The common misconception: more indexes is always better
An index is not a free accelerator; it trades write speed and disk space for read speed. On an attendance table taking thousands of writes a day, scattering a dozen indexes produces no visible read gain, measurably slower writes and bloated backups.
The second misconception is "slow site equals underpowered server". In more than half the projects we have inherited, the cause was N+1 queries or missing indexes. Look at the queries first, then talk hardware.
Check yourself before the requirements meeting
- ☐ I know which screens and reports the new column appears in
- ☐ I confirmed the vendor manages schema changes with migrations
- ☐ I asked how rollback works and how long it takes
- ☐ I received a list of the three slowest queries
- ☐ I decided what existing records hold in the new column
Frequently asked questions
Does one extra field really take three days?
If the column only appears in the admin panel and never touches reports, exports or the API, 3 to 4 hours is realistic. The cheapest way to save money is to state during requirements whether the field needs to be exported or reported on, rather than adding that after launch.
Why does the site slow down as data grows? It was fast in testing.
Test environments usually hold a few dozen fake records, and at that volume missing indexes and N+1 queries are invisible. Once real data reaches hundreds of thousands of rows, full table scans and repeated round trips surface. Ask for a load test using near-production data volume before sign-off.
Our system is already slow. What should we do first?
In order: pull the slow query list, add the critical indexes, fix the N+1 queries, and only then consider more hardware. The first two are cheap and produce the most visible gain, typically bringing the worst pages back to acceptable within one or two days of work.
Want to know which part of your system is slow?
ScriptWalker offers a free 30-minute technical consultation. Bring your slowest page and we will tell you on the spot whether it is a missing index, an N+1, or a data structure that needs restructuring, and hand you a concrete question list to take to your current vendor.
- Email: [email protected]
- Phone: 0916-224-047
- LINE: @ufv9089p