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

Answer 1

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.