Tag: Database Index

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

.tips { margin-top: 0px; text-align: left; max-width: 90%; padding: 20px 20px 30px 30px; background-color: #fffa8b; background-image: url(/_file/blog/_img/bulb.png); background-position: top 20px right 20px; background-repeat: no-repeat; text-indent: 0px; border: 0px solid #360063; border-radius: 8px; -webkit-border-radius: 8px; -moz-border-radius: 8px; -khtml-border-radius: 8px; } 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.

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:

SQL Server – Indexing Strategies: Clustered, NonClustered, and Filtered Indexes

This article reviews optimal placement of clustered and nonclustered indexes on OLTP databases, and explains how filtered indexes can be used to improve performance. Clustered Indexes By default, SQL Server will create the table’s clustered index during the creation of the primary key: CREATE TABLE PrimaryKeyTest (MyPK INT PRIMARY KEY) GO SELECT * FROM Sys.Indexes WHERE Object_ID = Object_ID ('PrimaryKeyTest') This can be overridden by specifying the NONCLUSTERED keyword during creation:

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.