Impact of Multi-Column Indexes in Database Systems

What is multiple-column index?

MySQL or many RDBMS supports multiple-column indexes. i.e. The index key will be more than 1 column in the table. For example, consider a table consolidated

CREATE TABLE `consolidated` (
	  `consolidated_id` int(11) NOT NULL AUTO_INCREMENT,
	  `pub_id` int(11) DEFAULT NULL,
	  `cp_id` int(11) DEFAULT NULL,
	  `ro_id` int(11) DEFAULT NULL,
	  `country_id` int(11) DEFAULT NULL,
	  `views` int(11) DEFAULT NULL,
	  PRIMARY KEY (`consolidated_id`)
	) ENGINE=InnoDB;

You can have an index created on pub_id and cp_id like below

CREATE INDEX idx_multi ON consolidated_multi (pub_id, cp_id);

Performance Impact

If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table. A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.

Experiment

For our experiment we created 3 different tables with:

• consolidated
• consolidated_single
• consolidated_multi

CREATE TABLE `consolidated` (
	  `consolidated_id` int(11) NOT NULL AUTO_INCREMENT,
	  `pub_id` int(11) DEFAULT NULL,
	  `cp_id` int(11) DEFAULT NULL,
	  `ro_id` int(11) DEFAULT NULL,
	  `country_id` int(11) DEFAULT NULL,
	  `views` int(11) DEFAULT NULL,
	  PRIMARY KEY (`consolidated_id`)
	) ENGINE=InnoDB;
	CREATE TABLE `consolidated_single` (
	  `consolidated_id` int(11) NOT NULL AUTO_INCREMENT,
	  `pub_id` int(11) DEFAULT NULL,
	  `cp_id` int(11) DEFAULT NULL,
	  `ro_id` int(11) DEFAULT NULL,
	  `country_id` int(11) DEFAULT NULL,
	  `views` int(11) DEFAULT NULL,
	  PRIMARY KEY (`consolidated_id`)
	) ENGINE=InnoDB;	CREATE INDEX idx_1 ON consolidated_single (pub_id);
	CREATE INDEX idx_2 ON consolidated_single (cp_id);
	CREATE INDEX idx_3 ON consolidated_single (ro_id);
	CREATE INDEX idx_4 ON consolidated_single (country_id);	CREATE TABLE `consolidated2_multi` (
	  `consolidated_id` int(11) NOT NULL AUTO_INCREMENT,
	  `pub_id` int(11) DEFAULT NULL,
	  `cp_id` int(11) DEFAULT NULL,
	  `ro_id` int(11) DEFAULT NULL,
	  `country_id` int(11) DEFAULT NULL,
	  `views` int(11) DEFAULT NULL,
	  PRIMARY KEY (`consolidated_id`)
	) ENGINE=InnoDB;CREATE INDEX idx_multi ON consolidated_multi (pub_id, cp_id, ro_id, country_id);

Query and Results

For our reporiting query on the above 3 tables with 3 set of data loaded (6 Million, 10 Million, 20 Million rows).

Query:

select pub_id, cp_id, ro_id, country_id, sum(views) 
      from consolidated
      group by pub_id, cp_id, ro_id, country_id;
      
select pub_id, cp_id, ro_id, country_id, sum(views) 
      from consolidated_single
      group by pub_id, cp_id, ro_id, country_id;
      
select pub_id, cp_id, ro_id, country_id, sum(views) 
      from consolidated_multi
      group by pub_id, cp_id, ro_id, country_id;

Results:

Read Similar Blogs: