Index a table created with a select

#1

I am creating a table with a SELECT statement.
CREATE TEMPORARY TABLE XYZ
(SELECT A.key AS key, otherfields FROM A JOIN B on A.key = B.key);

I want to index the table XYZ on A.key.
Is there a way to indicate in the above that I want an index or do I
have to do an additional
ALTER TABLE XYZ ADD INDEX(key);

Thank you,
Joe

#2

On 09/08/11 17:54, Joseph Hesse wrote:

Quote:

I am creating a table with a SELECT statement.
CREATE TEMPORARY TABLE XYZ
(SELECT A.key AS key, otherfields FROM A JOIN B on A.key = B.key);

I want to index the table XYZ on A.key.
Is there a way to indicate in the above that I want an index or do I
have to do an additional
ALTER TABLE XYZ ADD INDEX(key);

Thank you,
Joe
You can state an index and even additional fields in the create

statement. See the manual.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

#3

On Aug 9, 11:54*am, Joseph Hesse <j... (AT) gmail (DOT) com> wrote:

Quote:

I am creating a table with a SELECT statement.
CREATE TEMPORARY TABLE XYZ
(SELECT A.key AS key, otherfields FROM A JOIN B on A.key = B.key);

I want to index the table XYZ on A.key.
Is there a way to indicate in the above that I want an index or do I
have to do an additional
ALTER TABLE XYZ ADD INDEX(key);

Thank you,
Joe

There is also the ANSI Standard "create index idx_name on tablename
(colname,,,)"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Index a table created with a select

comp.databases.mysql comp.databases.mysql

Index a table created with a select - 08-09-2011 , 10:54 AM

Re: Index a table created with a select - 08-10-2011 , 02:23 AM

Re: Index a table created with a select - 08-10-2011 , 05:27 PM