0

I am running MySQL server v5.7.18 on CentOS 7.

One of the tables is used as operations log for a web API, and has grown to 20 million rows.

Users search this table from a front end app which runs a query like this:

SELECT * FROM log_table WHERE some_long_id = '12345abdcef';

The some_long_id column is of type text, and contains UUID style alphanumeric ID.
Currently this query takes around 20 sec to complete.

I found that if I add a key on this column, the query runs almost instantly:
ALTER TABLE log_table ADD KEY (some_long_id(64));

This operation takes a long time, but does the job.
I tested it on an off-line dummy DB.

My question: Can I safely run the ADD KEY operation on the production DB without taking down the web app?

There is an API that constantly inserts new rows in to this table, possibly several times per second.

Will this effect the indexing? Could it break something? Will inserts fail while the table is being indexed?

Edit: Forgot to mention it is using the innoDB engine.

P.S. I am not a DBA, I just have to manage this thing so my whole approach may be wrong.
Answers of "do this instead" are also welcome.

Also, google search does not give relevant results on this question, sorry if it is a duplicate.

Improve this question
2
  • "I tested it on an off-line dummy DB." - I guess that means it wasn't actively receiving logs, but is it roughly the same sized Table as the production?
    – J.D.
    Sep 17, 2022 at 12:44
  • @J.D.Yes, it was same size and same schema table, but with nothing writing to it when I added the key.
    – Lev M.
    Sep 17, 2022 at 13:39

1 Answer 1

Reset to default
1

IIRC mysql has supported online secondary index creation for some time, assuming you are using a decent table type like InnoDB: https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html “the table remains available for read and write operations while the index is being created”.

Your application may see some interactive performance drop while a big operation like that commuters for IO, but you shouldn't lose any of it completely.

For your own piece of mind, to make sure nothing blocks or slows more than your users will accept, perhaps try running an instance of the application against a copy DB while you repeat your previous test.

Improve this answer
1
  • Thanks! I am working on a Python script to simulate the logging operations to create some stress on the test DB, initially I was going to use it just to test effects on the select duration, but it should work for this too. My concern was some damage that is not readily visible (no immediate error responses) but the documentation you linked suggests it should be fine.
    – Lev M.
    Sep 17, 2022 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.