Notes

MySQL indexes notes

author: Paul Kim

categories: mysql

tags: mysql

MySQL Indexes

  • INDEX (duplicates allowed)
  • UNIQUE (duplicates not allowed)
  • FULLTEXT (full-text search)
  • SPATIAL
  • PRIMARY

Create indexes during table creation

you can create indexes during table creation using the CREATE TABLE Syntax

-- create table with index (duplicates allowed)
CREATE TABLE my_table (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 VARCHAR(45) NULL,
    PRIMARY KEY (col1),
    INDEX idx_name (col2, col3)
);

-- create table with unique index
CREATE TABLE my_table (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 VARCHAR(45) NULL,
    PRIMARY KEY (col1),
    UNIQUE INDEX idx_name (col2, col3)
);

-- create table with fulltext index
CREATE TABLE my_table (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 VARCHAR(45) NULL,
    PRIMARY KEY (col1),
    FULLTEXT INDEX idx_name (col2, col3)
);

Create indexes after table creation

you can create indexes after table creation using the ALTER TABLE Syntax or the CREATE INDEX Syntax

-- create index (duplicates allowed)
ALTER TABLE my_table ADD INDEX idx_name (col2, col3);
-- or
CREATE INDEX idx_name ON my_table (col2, col3);

-- create unique index
ALTER TABLE my_table ADD UNIQUE INDEX idx_name (col2, col3);
-- or
CREATE UNIQUE INDEX idx_name ON my_table (col2, col3);

-- create fulltext index
ALTER TABLE my_table ADD FULLTEXT idx_name (col2, col3);
-- or
CREATE FULLTEXT INDEX idx_name ON my_table (col2, col3);

Show table indexes

you can see table indexes using the SHOW INDEX Syntax

-- show all indexes in 'my_table' in 'my_db'
SHOW INDEX FROM my_table from my_db;
-- or
SHOW INDEX FROM my_db.my_table;

Drop index from table

you can drop index from table using the ALTER TABLE Syntax or the DROP INDEX Syntax

ALTER TABLE my_table DROP INDEX idx_name;

DROP INDEX idx_name ON my_table;

Indexes with names vs without names

In MySQL, we can give a name to indexes. If we don't give a name to the index, MySQL will automatically name the index for you.

create an index named idx_name

CREATE TABLE my_table (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 VARCHAR(45) NULL,
    PRIMARY KEY (col1),
    INDEX idx_name (col2, col3)
);

ALTER TABLE my_table ADD INDEX idx_name (col2, col3);

CREATE INDEX idx_name ON my_table (col2, col3);

create an index without a name

CREATE TABLE my_table (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 VARCHAR(45) NULL,
    PRIMARY KEY (col1),
    INDEX (col2, col3)
);

ALTER TABLE my_table ADD INDEX (col2, col3);

-- this does not work. CREATE INDEX requires an index name
-- CREATE INDEX ON my_table (col2, col3);

Note: Always name your indexes and prepend with idx_ for easy identification of indexes.


Index prefixes

create an index that uses only the first N characters of the column

Note: When you index a BLOB or TEXT column, you must specify a prefix length for the index

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Column indexes vs Multiple-column indexes

Note: The maximum number of indexes per table and the maximum index length is defined per storage engine. However, all storage engines have at least 16 indexes per table and a total index length of at least 256 bytes.

Source

ChromeEdgeFirefoxOpera

© 2021 paulkode.com. All rights reserved.