CODEWITHSUNDEEP
mysqlsqlmariadb
HyperActive
HyperActive

Reputation: 1316

how to index a column in MySQL within "CREATE TABLE" command

Is there a way to index the non-unique email column below within the CREATE TABLE command?

Instead of this:

CREATE TABLE addresses (
    phone_number VARCHAR(12) PRIMARY KEY,
    email TINYTEXT,
);
ALTER TABLE addresses ADD INDEX (email);

something like this:

CREATE TABLE addresses (
    phone_number VARCHAR(12) PRIMARY KEY,
    email TINYTEXT INDEX,
);

Upvotes: 0

Views: 807

Answers (4)

Ergest Basha
Ergest Basha

Reputation: 8973

You have to specify the key length for text key. Try:

CREATE TABLE `addresses`(
phone_number VARCHAR(12),
email TINYTEXT,
PRIMARY KEY `phone_number`(`phone_number`) ,
KEY `email` (`email`(255)));

KEY and INDEX are synonyms in MySQL

Upvotes: 1

James
James

Reputation: 3015

Is this what you want?

CREATE TABLE addresses (
    phone_number VARCHAR(12) PRIMARY KEY,
    email TINYTEXT,
    INDEX ix (email)
);

By default, all indexes are not unique.

Upvotes: 1

BrianZ
BrianZ

Reputation: 146

This creates and index named ix_email on the column email at table creation time.

CREATE TABLE addresses (
    phone_number VARCHAR(12) PRIMARY KEY,
    email TINYTEXT,
    INDEX ix_email (email)
);

Upvotes: 1

Christophe
Christophe

Reputation: 696

Very easily:

CREATE TABLE addresses (
    phone_number VARCHAR(12) PRIMARY KEY,
    email TINYTEXT,
    INDEX index_email (email)
);

(Source)

Upvotes: 2

Related Questions