Hello ALL,

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


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.


Code:
TempTable1
Name Salary
Bob 1000
Mary 1200
John 1000
Tom 1300
David 2000

TempTable2
Name Bonus
Bob 1000
Mary 1200
John 1000

TempTable2 and TempTable2 both created by other SQL statements.

I have to do the following SQL statement over the two temporary tables

Code:
SELECT TempTable1.Name, SUM(TempTable1.Salary + TempTable2.Bonus) AS Total
From
TempTable1
LEFT Join
TempTable2
On
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?

Thanks