Create a temporary table in MySQL with an index from a select

Question

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

User · Accepted Answer

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

User · Answer

I wrestled quite a while with the proper syntax for CREATE TEMPORARY TABLE SELECT   Having figured out a few things  I wanted to share the answers with the rest of the community   Basic information about the statement is available at the following MySQL links   CREATE TABLE SELECT and CREATE TABLE   At times it can be daunting to interpret the spec   Since most people learn best from examples  I will share how I have created a working statement  and how you can modify it to work for you    Add multiple indexes  This statement shows how to add multiple indexes  note that index names - in lower case - are optional    CREATE TEMPORARY TABLE core my tmp table   INDEX my index name  tag  time   UNIQUE my unique index name  order number   SELECT   FROM core my big table WHERE my val   1  Add a new primary key   CREATE TEMPORARY TABLE core my tmp table   PRIMARY KEY my pkey  order number   INDEX cmpd key  user id  time   SELECT   FROM core my big table  Create additional columns  You can create a new table with more columns than are specified in the SELECT statement   Specify the additional column in the table definition   Columns specified in the table definition and not found in select will be first columns in the new table  followed by the columns inserted by the SELECT statement   CREATE TEMPORARY TABLE core my tmp table   my new id BIGINT NOT NULL AUTO INCREMENT    PRIMARY KEY my pkey  my new id   INDEX my unique index name  invoice number   SELECT   FROM core my big table  Redefining data types for the columns from SELECT  You can redefine the data type of a column being SELECTed   In the example below  column tag is a MEDIUMINT in core my big table and I am redefining it to a BIGINT in core my tmp table   CREATE TEMPORARY TABLE core my tmp table   tag BIGINT  my time DATETIME    INDEX my unique index name  tag    SELECT   FROM core my big table  Advanced field definitions during create  All the usual column definitions are available as when you create a normal table   Example   CREATE TEMPORARY TABLE core my tmp table   id INT UNSIGNED NOT NULL AUTO INCREMENT PRIMARY KEY  value BIGINT UNSIGNED NOT NULL DEFAULT 0 UNIQUE  location VARCHAR 20  DEFAULT  NEEDS TO BE SET   country CHAR 2  DEFAULT  XX  COMMENT  Two-letter country code     INDEX my index name  location   ENGINE MyISAM  SELECT   FROM core my big table

User · Answer

CREATE  TEMPORARY  TABLE  IF NOT EXISTS  tbl name   create definition        table options  select statement   Example     CREATE TEMPORARY TABLE IF NOT EXISTS mytable  id int 11  NOT NULL  PRIMARY KEY  id   ENGINE MyISAM  INSERT IGNORE INTO mytable SELECT id FROM table WHERE xyz

[mysql] Create a temporary table in MySQL with an index from a select

Examples related to mysql

Examples related to ddl

Examples related to temp-tables