Create a temporary table in MySQL with an index from a select
ddl, mysql, temp-tables
Solution
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...
Problem
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; ```