Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: Is it possible to create index for a temporary table

    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

    Code:
    Create Temporary Table Tmp
      AS Select 
            Tb1.filed1  AS field1
        FROM
            Tb1 LEFT Join Tb2
        On
            Tb1.field1 = Tb2.field1
    Code:
          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.


    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
    Last edited by cy163; 12-02-08 at 08:14.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •