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';
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.
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.
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.