A new application feels fast because its tables are nearly empty. The same queries can crawl once real data arrives. The common causes, and how to find them before your users do.
Almost every application is fast on the day it launches. The tables hold a few dozen rows, every query returns instantly, and nobody has reason to worry. Then the business grows, the orders table reaches a hundred thousand rows, and pages that once loaded in a blink begin to take seconds. The code did not change. The data did. Here are the mistakes we see most often, all of which are cheap to avoid at the start and expensive to fix later.
The first is missing indexes. An index lets the database jump to the rows it needs, much as the index of a book saves you from reading every page. Without one, a query that filters on a column has to scan the entire table. With a thousand rows you will not notice. With ten million you certainly will. As a rule, columns you regularly filter on, join on or sort by are candidates: foreign keys, status columns, dates, email addresses used for login. Do not index everything, since each index slows down writes and takes space, but do index deliberately.
The second is not looking at how queries actually run. MySQL will tell you if you ask. Putting EXPLAIN in front of a query shows whether it uses an index or scans the whole table, and roughly how many rows it expects to examine. A number in the hundreds of thousands next to a query that should return ten rows is a clear warning. Turning on the slow query log in a test environment finds the worst offenders without guessing.
The third is the N+1 query problem. It appears when code loads a list of records and then runs one extra query for each record to fetch something related, such as the customer name for each of a hundred orders. That is a hundred and one queries where one join would do. It is invisible in development with five rows and crippling in production. Most frameworks can load related data in a single additional query, and it is worth learning how yours does it.
The fourth is selecting more than you need. Using SELECT * on a wide table pulls back every column, including large text fields, even when the page displays two of them. Ask only for the columns you use. Similarly, never load a whole table into memory and filter it in code. The database is far better at filtering than your application is.
The fifth is a search written as LIKE with a percent sign at the start, for example matching any name that contains a fragment. A leading wildcard cannot use an ordinary index, so every row is examined. For real search features, use a full-text index or a dedicated search tool instead.
The sixth is pagination done naively. Skipping a large number of rows with a big offset gets slower the deeper you go, because the database still has to read and discard everything before the page you asked for. For long lists, paginate by the last value seen, such as the last id, rather than by counting rows.
Two design habits also prevent trouble later. Use the right data types: dates in date columns rather than text, amounts in a decimal type rather than a floating-point one, and utf8mb4 as the character set so that names and emoji in any language store correctly. And use foreign keys, so that the database itself refuses orphaned or inconsistent data instead of relying on every piece of code to behave.
Finally, test with realistic volumes before you launch. A short script that fills the main tables with a few hundred thousand plausible rows will expose most of these problems in an afternoon. It is far easier to add an index on a quiet staging server than on a live system your customers are using.