Create a temporary table in MySQL with an index from a select

Question

I have a stored function where I use temporary tables. For performance reasons, I need an index in that table. Unfortunately, I cannot use ALTER TABLE because this causes an implicit commit.

Therefore I'm looking for the syntax to add the INDEX for tempid during creation. Can anyone be of help?

CREATE TEMPORARY TABLE tmpLivecheck 
(
    tmpid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
SELECT *
FROM   tblLivecheck_copy
WHERE  tblLivecheck_copy.devId = did;

Answer 1

Did find the answer on my own. My problem was, that i use two temporary tables for a join and create the second one out of the first one. But the Index was not copied during creation...

CREATE TEMPORARY TABLE tmpLivecheck (tmpid INTEGER NOT NULL AUTO_INCREMENT, PRIMARY    
KEY(tmpid), INDEX(tmpid))
SELECT * FROM tblLivecheck_copy WHERE tblLivecheck_copy.devId = did;

CREATE TEMPORARY TABLE tmpLiveCheck2 (tmpid INTEGER NOT NULL, PRIMARY KEY(tmpid), 
INDEX(tmpid))  
SELECT * FROM tmpLivecheck;

... solved my problem.

Greetings...