Unanswered: Is it possible to create index for a temporary table
I have to do SQL query over two temporary tables, I want to create indexes on fields of the temporary tables.
Is it possible to create index on a temporary table. I am using MySQl 5.0.26
Furthermore, Is it possible to merge the statements for creating temporary table and the statement for creating index on the temporary table into one singel statement
Create Temporary Table Tmp
Tb1.filed1 AS field1
Tb1 LEFT Join Tb2
Tb1.field1 = Tb2.field1
CREATE INDEX idx_field1 ON Tmp (field1)
The reason for me to use temporary table in my project is as follows. Temporary tables are session-specific. Several users can create temporary tables with the same name, and each user will see only his version of the table Multiple users can perform this operation at the same time with the same temporary names without fear of collision.
Actually, I have two temporary tables, TempTable1 and TempTable2.
TempTable2 and TempTable2 both created by other SQL statements.
I have to do the following SQL statement over the two temporary tables
SELECT TempTable1.Name, SUM(TempTable1.Salary + TempTable2.Bonus) AS Total
TempTable1.Name = TempTable2.Name
The execution of the above statement is very slow (takes 5 minutes), when there are 120,000 records in TempTable1 and 100,000 records in TempTable2.
I want to create indexes on Name field of TempTable2.
Does this idea make sense?