Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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."

share|improve this answer
    
When I set up an index on a temporary table just have a query run faster then I do have to wait for the index because it takes computational time to set it up for the DBMS. Please read my question again. –  Raffael Nov 1 '13 at 18:31
    
If you declare the index as part of the table definition, the index is maintained in real-time as you insert (and inserts will be proportionally slower); if you create the index after inserting, the index is ready as soon as your CREATE INDEX statement finishes executing and control returns to the client... not some period of time after that... so I don't understand what you mean by "wait" for the index. It's there as soon as the statement is done and in MEMORY the table is locked til then... if that doesn't address what you are asking, please provide more info on who or what is waiting. –  Michael - sqlbot Nov 1 '13 at 19:14
    
Aaaah ... okay, so the index is set up while inserting - I thought that (maybe) the insertion will trigger an independent indexing process that will run most likely beyond the insertion statement. but if the indexing is done at the same time and insertion is consequently slowed down then it all makes sense. just didn't want to fool myself running the query before the index is ready when the index is the primary concearn of the preparational statements. –  Raffael Nov 1 '13 at 19:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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