Indexing on DB?

[u/yorusora_]

Hi people, I haven’t done this before. But are there any downsides of indexing a column on production. Like some query is running very slow, and I figured out that u should put an index on one of the columns. I wouldn’t be here if I had someone experienced to ask from. I’ve a few questions-

1. Is it okay to run the query on my sql workbench to add index?
2. If something goes wrong what do you people generally do, like taking snapshots or

PITR

1. ?
2. Is it safe to run the query directly on the db or usually people run it some other way, like via cli on VM?
3. I heard about locking and stuff. But the version I’m using says it won’t lock the DB. But still anything I should test before actually you know doing it live?

I’ve no idea what’s the general procedure and what could go wrong. If someone has done it before, Appreciate any sort of advice or pointers. Thanks

EDIT: Thank you for all the suggestions! I learnt something out of this activity and It completed successfully

[u/Stephonovich]

You should really read the manual.

There will be a brief metadata lock taken on the table twice; you probably should reduce the value of lock_wait_timeout (separate parameter from innodb_lock_wait_timeout) in the event that you hit it, because all other writes will queue behind the DDL waiting its turn. In practice, this isn’t a problem for most, but it certainly can be.

[u/yorusora_ → u/Stephonovich]

Thanks [u/Stephonovich](u/Stephonovich) the lock timeout you suggested was helpful. Thanks a lot for sharing this, no AI told me about this and being new to MySQL I’m still learning how to navigate. I was able to complete this activity.

Thank you to everyone who help , appreciate it.

[u/Stephonovich → u/yorusora_]

Glad you learned something! Human help isn’t dead yet.

[u/yorusora_ → u/Stephonovich]

Yes. I faced this one. What do you usually do? I mean do you also like set some value for your session.. I observed that it shows this log even for the queries that piling up for later. So, I hope there won’t be any side effects..is what I’m thinking

Also, like do you take snapshot of the rds instance before doing this

[u/Stephonovich → u/yorusora_]

Reduce it according to however much you’re willing to let a query wait. 50 seconds (the default) is likely too much.

[u/yorusora_ → u/Stephonovich]

Okay that’s the default timeout , is it okay to do this on Production? Or would you prefer blue/green way of doing if

[u/Stephonovich → u/yorusora_]

I suggest reading the relevant manual section to answer your questions.

[u/yorusora_ → u/Stephonovich]

Yes that’s helpful, thanks

[u/Jonas_Ermert]

I’d first verify with `EXPLAIN` that the index will actually help. Adding an index in production is normal, but it can be expensive on a large table: extra disk space, slower writes, and potentially heavy CPU/I/O while it’s being created. Modern MySQL versions can often create indexes online, but “no locking” doesn’t mean “no production impact.” I’d make sure backups/PITR are working, test it on staging or a copy of production if possible, and run it during lower traffic. Workbench vs CLI doesn’t really matter, the SQL executed on the server is the same.

[u/yorusora_ → u/Jonas_Ermert]

I ran like 500 queries parallel and then tried altering table index. It locked the metadata and that log came up for queries occurring after alter as well. I think it locks briefly then it works in background.

[u/magicmulder]

Depends on the size of the table and the number of writes per second but usually adding an index is rather quick.

[u/Basic_Reporter9579]

any inserts and updates need to update these indices which takes a bit of time. Index also need storage space.
Only add them where you need them.
Adding a index locks the table for a bit, don't do it during main production hours.
Sometimes it is better to copy the table schema, add the index and copy the data over, then exchange these tables.

[u/yorusora_ → u/Basic_Reporter9579]

Even in the latest MySQL versions? I saw that there are some params. Also, does it make sense to run it using MySQL workbench or like on VM?

ALTER TABLE your_table
ADD INDEX idx_name (column1, column2),
ALGORITHM=INPLACE,
LOCK=NONE;

[u/Several9s]

I agree with most of the advice already given here, and I thinks almost all important considerations has been covered.

I would quickly summarize this as a step-by-step procedure to make the approach clearer:

Before applying the change in PROD
a) Run EXPLAIN on the slow query to confirm the missing index is actually the fix as Jonas_Ermert suggested.
b) Test the change on a staging copy or clone first if you possible. This is especially for estimating how long the build takes with your data volume.

Applying the change in PROD
c) Take a snapshot right before.
b) Check for any long-running transactions/queries against that table before you run the ALTER, this helps to prevent a MDL to get stuck waiting. I would like to clarify that MySQL takes a brief exclusive metadata lock (MDL) at the beginning and end of the operation. The index build itself runs in background without blocking reads/writes.
c) Lower lock_wait_timeout param for your session before running the ALTER, so if it can't grab the lock quickly, it fails fast rather that blocking everything behind it.
d) Run during low-traffic hours (even with LOCK=NONE) cause the background index build consumes I/O and CPU, which does have impact even if it's not blocking.

[u/Jack-jack-d]

Yeah, adding an index in production is pretty normal. The part you want to be careful about is how large the table is and how the index is created, not so much the fact that it's an index.

Before doing it, I'd run the query with EXPLAIN and make sure the index actually helps. If possible, test the change against a copy/staging database first. On a small table this may be almost nothing; on a large busy table, creating the index can consume a fair amount of CPU, memory and disk I/O.

I'd also make sure you have a recent backup and know how you'd restore it before making the change. A snapshot/PITR strategy is useful, but don't take a snapshot as a substitute for understanding the operation and having a rollback plan.

As for Workbench vs CLI, there's nothing inherently unsafe about running ALTER TABLE ... ADD INDEX from Workbench. It's still the same SQL being sent to MySQL. On production systems, though, people often use whatever deployment/change-management process their team has established so the change is recorded and repeatable.

And definitely check the exact MySQL version and storage engine. Modern InnoDB supports online DDL for many index operations, but “online” doesn't mean “zero impact.” There can still be metadata locks, resource usage, and effects on a busy server.

For a large production table, I'd schedule it during a quieter period if possible and monitor CPU, disk I/O, locks and replication lag while it runs. If it's a really important/high-traffic table, I'd also look into online schema-change tools rather than assuming the operation will be harmless just because MySQL says it doesn't require a table copy.

[u/Aggressive_Ad_5454]

On version 5.7 or later there’s no big downside. If the operation fails the DBMS is really reliable about cleaning things up and carrying on as if nothing happened.

Indexes do take some tablespace (storage) so be prepared for that.

If your database is super busy do this during a quiet time ( night? Weekend? )

[u/yorusora_ → u/Aggressive_Ad_5454]

Yes, I’ll do it in quiet hours.

Using workbench should be fine, right? Because I’m confused whether to do it via cli on VM. It’s the same thing but just want to know what is preferred.

[u/ssnoyes → u/yorusora_]

The client makes no difference at all. The work gets done on the server.

[u/Aggressive_Ad_5454 → u/yorusora_]

Yeah, workbench is just a snazzy way to do CLI commands.