An index suggestion from a language model is a hypothesis, not a patch. The moment you paste a slow query into a chat window, you are asking a system that has never observed your database's actual I/O patterns to make a physical design decision. The model may return a plausible CREATE INDEX statement and a confident explanation about why that index will help, but what you need is a measured change in work, not a fluent justification. If you accept the suggestion and move on, you are letting a text prediction tool act like a database administrator, which is exactly where harmless-looking DDL turns into a production write regression. The practical workflow is to make the model generate both the candidate index and a narrow set of queries that should get cheaper, then run those queries before and after on an isolated table. That turns an assertion into an experiment.
When you have access to a free model and a free server option, the two halves of that experiment become almost disposable. You can ask MonkeyCode's free model access to propose an index for your schema and query, then use the free server option to run the same workload against an isolated copy of the data instead of your development machine. Disclosure: This article was prepared as part of MonkeyCode's product outreach. These two capabilities are relevant because they lower the cost of being wrong, which matters a lot when the failure mode is not a syntax error but a plausible performance claim. A model can tell you that status needs an index, and that sentence will always sound reasonable even when status has only three values and the planner would choose a full scan anyway. Running the workload before and after is how you catch the gap between a likely-sounding explanation and what your engine is actually going to do.
The artifact you need is not complicated. You want a small harness that builds a table, fills it with enough rows to make a scan visible, records the query plan and wall-clock time for a representative query, applies the candidate index, and records the same two measurements again. The listing below is a Python sketch using SQLite's EXPLAIN QUERY PLAN, and it is meant to be read as a working pattern rather than a copy-paste guarantee. If your real database is PostgreSQL or MySQL, you would replace the plan function with the equivalent command from your engine, but the shape of the experiment stays the same.
import random
import sqlite3
import time
def build_table(con):
con.execute('''
create table purchases (
id integer primary key,
customer_id integer not null,
status text not null,
amount_cents integer not null
)
''')
rows = [
(i, random.randint(1, 50_000), 'paid' if i % 4 else 'open', random.randint(100, 99_999))
for i in range(1, 200_001)
]
con.executemany(
'insert into purchases (id, customer_id, status, amount_cents) values (?, ?, ?, ?)',
rows,
)
def measure(con, label):
customers = random.sample(range(1, 50_001), 300)
start = time.perf_counter()
for cust in customers:
con.execute(
'select count(*) from purchases where customer_id = ? and status = \'paid\'',
(cust,),
).fetchone()
elapsed = time.perf_counter() - start
plan = con.execute(
'explain query plan select count(*) from purchases where customer_id = ? and status = \'paid\'',
(999,),
).fetchall()
print(f'{label}: {elapsed:.3f}s')
for row in plan:
print(' ', row)
con = sqlite3.connect(':memory:')
build_table(con)
measure(con, 'before')
con.execute('create index idx_purchases_customer_status on purchases (customer_id, status)')
measure(con, 'after')
If you run this, you are looking for two things at once. The EXPLAIN QUERY PLAN output should change from a full scan of the entire table to an index lookup on the columns in your predicate, and the elapsed time should drop by enough to matter. The reason you check both is that a planner can use an index while the workload still gets slower because the index forced a new kind of read pattern or because the timing was dominated by something else. The measurement is also noisy, so a single run is not the end of the exercise. Run the before and after blocks a few times, restart the connection, and watch whether the improvement survives the noise instead of disappearing when the data is warm. If the plan changes but the time does not, the index is doing less than the model's explanation suggested.
That is where the free server option becomes useful beyond simply running code on your laptop. Many indexing decisions fail not because the DDL is wrong, but because the local SQLite database or a small local PostgreSQL container does not behave like the engine and data volume you have in production. A free disposable server lets you repeat the same before-and-after measurement on a second copy of the data, which catches the cases where your local experiment was too small to show the real cost or too artificial to match your query mix. The model's free access gives you the candidate index quickly, but the server option is the part that stops that candidate from becoming a patch you apply with no evidence. You do not need a cloud account or a permanent deployment for this; you only need an isolated table that can be thrown away after the test.
The model is best used as a generator of hypotheses, not as an authority. A better prompt is not 'Is this query slow?' but 'Given this schema and this query, propose one index and tell me the smallest test that would show it helps.' That forces the model to name the columns, the query, and the expected plan change. You can then take that proposed test and run it exactly as written, or you can reject the prompt's suggestion if it cannot express the benefit in a measurable way. A model that offers only a paragraph about cardinality and scan reduction has not given you a test; it has given you a reason to keep looking. The same principle applies when you ask the model to revise an existing index. If the new index is supposed to remove a sort step, your before and after plan should show the sort step disappearing, not just a different set of internal row estimates.
There are real limitations to this workflow, and they are worth stating plainly. A measured improvement on an isolated table tells you that the index helps that specific query against that specific data distribution, but it does not tell you what the index will do to insert throughput, storage, or lock contention. A covering index that speeds up one report can make every write slightly more expensive, and the harness above is not designed to catch that cost. The free server option is also not a magic reproduction of your production system; it removes some inconvenience, but it cannot reproduce your real lock wait events, replication lag, or the effect of long-lived transactions. If the query's pain only appears under a particular concurrency pattern, a sequential before-and-after test will miss it because the harness is exercising the table one query at a time.
You should not use this approach when you already know the workload is small enough to fit in memory, because the index will not produce a healthy measurable effect and the experiment will teach you more about timer noise than about the database. You should also avoid it when you do not have a reliable query pattern to test, because choosing a random set of customer_id values in the script may not resemble the ten percent of customers whose queries are actually slow. The method helps most when you have a specific slow query and a concrete candidate index, not when you are searching a whole schema for vague optimization opportunities. A free model can list many possible indexes, but each one is still a separate hypothesis, and treating them as a batch of patches is how you end up with five overlapping indexes that nobody can explain.
The value of this work is not that it makes database optimization automatic. It is that it moves the decision from the model's language to the database's own planner. When you can show the before and after plan side by side, you no longer have to argue about whether the model's explanation was convincing; you can simply say that this query stopped scanning two hundred thousand rows when the index was applied. If you already have MonkeyCode's free model and server option, this is a useful pattern to keep in a scratch project, because the whole point is to replace a confident answer with a small measured result. That is a narrower goal than 'let AI tune my database,' but it is also the version you can actually trust when the migration runs next week.
Top comments (0)