MySQL temp table issue

Question

Hi folks! I'm trying to use temp tables to speed up my MySQL 4.1.22-standard database and what seems like a simple operation is causing me all kinds of issues. My code is below....

CREATE TEMPORARY TABLE nonDerivativeTransaction_temp (
   accession_number varchar(30), 
   transactionDateValue date)
) TYPE=HEAP;

INSERT INTO nonDerivativeTransaction_temp 
VALUES( SELECT accession_number, transactionDateValue 
          FROM nonDerivativeTransaction 
         WHERE transactionDateValue = "2010-06-15");

SELECT * 
  FROM nonDerivativeTransaction_temp;

The original table (nonDerivativeTransaction) has two fields, accession_number (varchar(30)) and transactionDateValue (date).

Apparently I am getting an issue with the first two statements but I can't seem to nail down what it is. Any help would be appreciated.

Answer 1

Drop the VALUES( in INSERT INTO ... VALUES ( SELECT, it's either VALUES() or SELECT, not both.

And normally this setup of yours would slow down things rather then speed them up unless you're querying the temporary table a LOT during the session, and query-caching is off and/or not feasible.

Answer 2

A quick look makes me wonder if the mismatched parens might be part of your problem...