Mysql – Query when index is ready

indexmemoryMySQL

To improve performance of a query I set up an in-memory table and fill it with data. Of course to increase speed I will use an index on the data stored in the in-memory table.

create table helper (
    id int, index using hash(id)
) engine = memory;

insert into helper (id)
select id from source;

[select that uses IDs stored in helper]

Now the problem I anticipate is that the actual query will be run prematurely – before the index is ready to use. How do I know how long I have to wait?

I think executing CREATE INDEX ... does the job because it will halt the sequence of SQL statements until it is done. But it seems odd to create the index again, when the propper place to set up an index is within the CREATE statement of the table.

(I am using MySQL)

Best Answer

Indexes in MySQL are maintained synchronously. When an insert/update/delete query (or an alter table to add, remove, or enable indexes) finishes running, the indexes are consistent with the table's data.

There is no such thing in MySQL as an index being in a state that is not "ready to use."

Related Question