Tag: Database Index
What Are the Types of Indexes in a Relational Database?
Using database indexes is one of the easiest ways to improve the overall performance of a database, more specifically query performance, if you select the right type. Knowing the types of indexes in SQL is very important since different indexes provide different benefits. We review commonly used indexes from the most popular RDBMS and explain when to use them.
What Are Database Indexes? A database index is an additional data structure created on top of the data in a table.
What Is a Database Index, and What Does It Do?
Understand what a database index does and how it can improve SQL query performance.
Modern databases store terabytes or petabytes of data and are a key resource for organizational operations and decision-making. A database index is a special data structure that allows quick access to specific pieces of information without having to read all data stored in a particular table. They ensure database performance in transactional environments.
How Does a Database Index Enhance Access to Information?
An Introduction to MySQL Indexes
We’re all familiar with the indexes in books: they help you find specific contents much faster by telling you where they’re located. In a nutshell, database indexes essentially do the same thing—they let you retrieve information from a database much faster by narrowing down the scope of your search.
In fact, creating indexes for database tables is one of the most important concepts of database modeling. It’s also often one of the first things you should consider if your query is running too slowly, in which case it may benefit from indexing.
All About Indexes Part 2: MySQL Index Structure and Performance
Database indexes speed up data retrieval operations. But there is a price we pay for these benefits.
In this article, we’ll focus on the structure behind the MySQL index. We’ll also measure database performance by using large datasets to test two versions of the same database – one with indexes and the other without them.
This is the second article in our series about database indexes. If you missed the first one, click here.
All About Indexes: The Very Basics
There are many things that impact database performance. The most obvious is the amount of data: the more you have, the slower your database will be. While there are many ways to fix performance problems (like removing old data or using denormalization), the primary solution is to properly index your database. In this series, we’ll explain several very important indexing concepts, starting with the basics and ending with best practices.
Why Does Oracle Sometimes Not Drop an Index Associated with a Primary Key or Unique Constraint?
Database schema migration is never an easy job. In fact, it can really be a headache, even when you’re working with a familiar system. For example, at times Oracle 10g may not drop the associated index for a primary key or unique constraint that has been dropped. In this article, I am going to explain when and why this happens.
The Story: I’ve been working on the development of an e-commerce platform.
Tip #4 – How to Make a Column Unique
Sometimes there are columns in a table that don’t belong to primary key, but are still unique. To mark them as a unique, you have to create an alternate (unique) key containing it.
Single-column alternate (unique) key Select the table with the column you want to make a unique. Then, click the Alternate (unique) key tab in the Table properties panel on the right:
Click Add key:
ColumnStore Indexes in MS SQL Server
Introduced in SQL 2012, ColumnStore indexes differ greatly from standard row-based indexes. Intended for OLAP systems, these indexes store data in a highly compressed, segmented fashion with the column as the basis (rather than typical row-based indexes). This type of column-based index allows for great performance gains in data warehouses where table scans, rather than seeks, are performed.
ColumnStore indexes have evolved significantly over the last few SQL Server versions:
Virtual Column
The concept of views and function-based indexes has been known for many years. One of the brand new solutions is a virtual column – a feature introduced in Oracle 11g. Apart from database giant, some well known DB vendors, like MariaDB and SQL Server, support the idea of computed columns. So let’s give virtual columns a try and examine their basic usage.
Generally, there are two kinds of virtual columns:
What Is a Database Index?
Sooner or later there comes a moment when database performance is no longer satisfactory. One of the very first things you should turn to when that happens is database indexing. This article will give you a general overview on what indexes are without digging into too much detail. We’ll discuss additional database index topics in future articles.
In general, a database index is a data structure used to improve queries execution time.
Database Indexing – Whose Role Is It Anyway?
I remember my own confusion about who was responsible for database indexing when I was a junior programmer some years ago. At one of my very first commercial projects, software architects created a database structure, developers wrote the code, and browser magicians made it look outstanding. The final product was deployed to servers and champagne corks popped. Our good mood didn’t last for too long...
After three months or so it turned out that one of the main features, a search engine, started choking.
SQL Performance Explained – the must-read book
Some time ago, the Vertabelo Team participated in the PostgreSQL Conference Europe 2013. Some of the talks were really nice. One of them stuck in my head for quite a long time. It was Markus Winand’s lecture titled “Indexes: The neglected performance all-rounder.” Although I had had a solid background in databases, this 50 minutes long talk showed me that not everything concerning indexes was as clear to me as I had thought.