MySQL - multiple column index

Question

I'm learning MySQL index and found that index should be applied to any column named in the WHERE clause of a SELECT query.

Then I found Multiple Column Index vs Multiple Indexes.

First Q, I was wondering what is multiple column index. I found code bellow from Joomla, is this Multiple Column Index?

CREATE TABLE `extensions` (
    `extension_id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `element` VARCHAR(100) NOT NULL,
    `folder` VARCHAR(100) NOT NULL,
    `client_id` TINYINT(3) NOT NULL,
    ... ...
    PRIMARY KEY (`extension_id`),

    // does code below is multiple column index?

    INDEX `element_clientid` (`element`, `client_id`),
    INDEX `element_folder_clientid` (`element`, `folder`, `client_id`),
    INDEX `extension` (`type`, `element`, `folder`, `client_id`)
)

Second Q, am I correct if thinking that one Multiple Column Index is used on one SELECT ?

SELECT column_x WHERE element=y AND clinet_id=y; // index: element_clientid

SELECT ex.col_a, tb.col_b
    FROM extensions ex
    LEFT JOIN table2 tb
    ON (ex.ext_id = tb.ext_id)
    WHERE ex.element=x AND ex.folder=y AND ex.client_id=z; // index: element_folder_clientid

Marc B · Accepted Answer · 2013-11-28 20:08:43Z

25

General rule of thumb for indexes is to slap one onto any field used in a WHERE or JOIN clause.

That being said, there are some optimizations you can do. If you KNOW that a certain combination of fields are the only one that will ever be used in WHERE on a particular table, then you can create a single multi-field key on just those fields, e.g.

INDEX (field1, field2, field5)

v.s.

INDEX (field1),
INDEX (field2),
INDEX (field5)

A multi-field index can be more efficient in many cases, v.s having to scan multiple indexes. The downside is that the multi-field index is only usable if the fields in question are actually used in a WHERE clause.

With your sample queries, since element and field_id are in all three indexes, you might be better off splitting them off into their own dedicated index. If these are changeable fields, then it's better to keep it their own dedicated index. e.g. if you ever have to change field_id in bulk, the DB has to update 3 different indexes, v.s. updating just one dedicated one.

But it all comes down to benchmarking - test your particular setup with various index setups and see which performs best. Rules of thumbs are handy, but don't work 100% of the time.

answered Nov 28, 2013 at 20:08

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

qaharmdz Over a year ago

Yes, I doing some test sort based on field1 and other field is indexed automatically. But I'm not sure about splitting element and field_id independently.

Collectives™ on Stack Overflow

MySQL - multiple column index

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related