mysql临时表索引,在MySQL中使用选择索引创建临时表

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;

解决方案

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