MySQL in AI products: reduce work before enlarging the server

A small query can be the largest consumer

A counter that scans a table every few minutes can use more disk work than an occasional report. A one-row update can change several index pages. Cost depends on work per execution multiplied by frequency, as well as response size.

In database operations I worked on, the review linked repeated queries, rows examined and physical writes. Increasing memory without identifying those routines would leave the same unnecessary work in the system.

Read the required range and avoid searching for absent work

I implemented reads bounded by range and recovery position. The index follows the query filters and ordering. Routines with no changes can wait for a cheap signal before repeating work. Counts need a path that does not open every record in a wide table.

The execution plan must confirm this behaviour. A query with a limit can still traverse an entire index. Check the plan with representative volumes and compare rows examined after the change.

Aggregate writes and separate recent input

Another decision I applied was to aggregate events by time window and write only changed values. Recent input can stay in a small table and merge into the main table in ordered ranges. This reduces the frequency of scattered changes in large indexes.

The application must read both parts during this separation and prevent duplicate counts. It must also preserve the merge recovery position. New indexes on large tables need a planned operation, with wait limits and verified impact.

Use a review based on measured work

Consulting can identify expensive routines, review plans and redesign periodic workloads. The comparison uses the same measures before and after. Capacity investment comes with an explanation of the remaining work and availability requirements.