Security on the web wouldn’t be the same without databases – and databases wouldn’t be the same without the components that make them work. One of such components – a crucial one at that – are indexes.
Indexes usually work differently depending on the database management system that is being used, but their core principle remains the same – they speed up read queries at the expense of slowing down writes.
Indexes do indeed look classy – at least for the most part. However, they certainly have their own downsides. In this blog, we look more into multicolumn indexes and their darker part.
What are Multicolumn Indexes?
Multicolumn indexes, also called composite indexes, are a type of index that store a sorted table of “pointers.” Composite indexes serve a specific purpose – queries that use all or at least some of the columns specified in the index will use a composite index.
Composite, or multicolumn, indexes are very useful when SELECT queries that are in use frequently use (read from) a specified set of columns or in certain queries making use of range expressions. When composite indexes are used properly, database management systems are able to avoid table scans.
Choosing a Composite Index
With that being said, choosing composite indexes isn’t always easy and choosing the order of indexes is a frequent source of confusion – it is advisable to first put the column that is used the most often, and then proceed with the other ones. Suppose one of the most frequent queries we run looks like so:
SELECT * FROM demo_table WHERE first_name = ‘John’ AND surname = ‘Surnamey’;
To satisfy such a query with a composite index, create an index like so:
CREATE INDEX demo_index ON demo_table(first_name,surname);
You get the idea – it’s that easy. Choose the column that is used the most frequently first, then follow up with all of the other ones.
The Implications of Composite Indexes
Composite indexes are a great idea to satisfy queries like the one shown above – however, they don’t come without any implications. One of the most frequent mistakes that DBAs and software engineers alike make in this space is improperly choosing their order – it is thus very important to evaluate your choices and choose carefully. If a composite index is placed improperly, it will not only occupy precious hard drive space, but make your application perform worse than you initially expect. It all comes to light with practice, though – play with indexes enough times and you will be able to make good decisions no matter what.
Also, keep in mind that contrary to popular belief, composite indexes don’t make all other indexes that might exist on a column useless – you will still be able to make good use of them if you run other types of queries (think about queries making use of FULLTEXT indexes, etc.)
Summary
Multicolumn or composite indexes in databases certainly have their own upsides – the main one of them being the fact that they can be used to satisfy queries that either frequently make use of a specified set of columns or those queries that use range expressions. We hope that this blog post has made the world of them a little more clear for you and we hope that you will consider reading up more about the world of databases. Make sure to ensure your safety by using all of the features provided by the data breach search engine, implement our API offering into the infrastructure of your company to ensure the safety of your employees, and we will see you in the next blog!