{"id":102811,"date":"2024-08-30T00:12:48","date_gmt":"2024-08-30T00:12:48","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=102811"},"modified":"2024-08-30T01:24:36","modified_gmt":"2024-08-30T01:24:36","slug":"mysql-index-overviews-composite-b-tree-indexes","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/mysql-index-overviews-composite-b-tree-indexes\/","title":{"rendered":"MySQL Index Overviews: Composite B-Tree Indexes"},"content":{"rendered":"

Indexes in MySQL are one of the primary ways to enhance query performance and they are especially useful when the primary use case of our project refers to reading data stored in a database. We\u2018ve already told you about the nuanced world of indexes in MySQL<\/a> \u2013 and there we\u2018ve mentioned that MySQL has multiple types of SQL indexes available for you to choose from.<\/p>\n

The primary index type available within MySQL is the B-Tree index which we\u2018ve already covered in one of our previous articles. If you know your way around MySQL though, you will certainly be aware of other nuances of indexes, too \u2013 and one of those nuances has to do with the fact that B-Tree indexes can also consist of multiple columns (commonly referred to as composite indexes). In this example, we\u2019re using MariaDB as our DBMS of choice. Percona Server for MySQL and MySQL Server will act identically.<\/p>\n

Find the queries recreating the table structure and composite indexes in the appendix, and let\u2019s get started.<\/p>\n

What are Composite Indexes?<\/h2>\n

In the database world, composite indexes refer to indexes on multiple columns. Composite indexes are sometimes referred to as multicolumn indexes because that\u2018s precisely what they are \u2013 such indexes are keys on multiple columns, while indexes on the same column are referred to as simple indexes.<\/p>\n

Multicolumn indexes have various specifics unique to themselves \u2013 depending on the database management system you find yourself using, you will find that composite indexes can be created on up to 16 or 32 columns, that these kinds of indexes only use B-Tree (MySQL) or GIN, BRIN, and GiST index types (PostgreSQL), and that a multicolumn index can be similar to a sorted array.<\/p>\n

Composite Indexes in MySQL<\/h2>\n

Let\u2018s not get too far away from our primary DBMS of interest \u2013 MySQL. As noted before, the specifics of the components within your DBMS depend directly on the database management system you find yourself using \u2013 that\u2018s true for every component including composite indexes.<\/p>\n

Composite indexes in MySQL:<\/p>\n