Database internals

Querying MySQL’s INFORMATION SCHEMA: Why? How?

by Emil Drkušić
9 Oct 2018 Databases need to run optimally, but that’s not such an easy task. The INFORMATION SCHEMA database can be your secret weapon in the war of database optimization. We’re used to creating databases using a graphical interface or a series of SQL commands. That’s completely fine, but it’s also good to understand a bit about what is going on in the background. This is important for the creation, maintenance, and optimization of a database, and it’s also a good way to track changes that happen ‘behind the scenes’.
Read more
Database internals

An Introduction to MySQL Indexes

by Francisco Claria
29 Nov 2017 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.
Read more
Database internals

Running Microsoft SQL Server on Linux

by Marian Dziubiak
29 Mar 2017 If you were a fan of Linux and SQL Server, you basically had to have two operating systems at all times. But now, Microsoft has made a Linux version of their relational database management system. How does it work, and how well does it perform? Microsoft products used to be Windows-centric, but in recent years Microsoft CEO Satya Nadella has started moving the company towards cloud-based and open-source projects and enabling their software to run on other platforms.
Read more
Database internals

Everything You Need to Know About MySQL Partitions

by Francisco Claria
10 Feb 2017 What is MySQL partitioning? What kind of partition types are there? How do you know if this is something your database engine supports? In this article, we tell you what you need to know about partitioning in MySQL. MySQL partitioning is about altering – ideally, optimizing – the way the database engine physically stores data. It allows you to distribute portions of table data (a.k.a. partitions) across the file system based on a set of user-defined rules (a.
Read more
Database internals

The Proper Way to Handle Multiple Time Zones in MySQL

by Francisco Claria
21 Dec 2016 In this article, I’ll walk you through some fundamental considerations for working with date- and time-related data in MySQL. We’ll also look at how to handle multiple time zones and daylight saving time changes. Let’s first address some core concepts that will help us understand the underlying complexity of time-related data. It is important to notice that these concepts apply when representing a point in time rather than an absolute duration.
Read more
Database internals

Supporting R in SQL Server 2016

by Dejan Sarka
7 Dec 2016 SQL Server R Services combine the power and flexibility of the open-source R language with enterprise-level tools for data storage and management, workflow development, and reporting and visualization. This article introduces SQL R Services and the R language. What Is R and How Is It Used? R is the most widely used language for statistics, data mining, and machine learning. (R is also the name of the environment and the engine that executes code written in the R language.
Read more
Database internals

JSON and PostgreSQL: A Powerful Combination

by Maria Alcaraz
11 Oct 2016 JSON is a data interchange format that is designed to be lightweight and easy to work with. It’s quite popular in web applications, and it can be considerably more flexible than a traditional relational data model. PostgreSQL 9.3 and later versions support JSON, so you can store JSON data and use native Postgres functions to operate on it. This includes decomposing, transforming, or even creating JSON data from regular relational data.
Read more
Database internals

An Overview of MySQL’s Information Schema

by Emil Drkušić
13 Sep 2016 Have you ever wondered what the DBMS (database management system) does when you create a new database, add tables and functions, or define primary keys? It’s not magic. The answer is actually very simple. The DBMS has a “system” database that it uses to store information about all the objects we’ve created in every database on that server instance. In this article, we’ll take a look at the MySQL INFORMATION_SCHEMA database.
Read more
Database internals

MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.

by Francisco Claria
23 Aug 2016 Whenever you need to save datetime data, a question arises about what MySQL type to use. Do you go with a native MySQL DATE type or use an INT field to store date and time info as a plain number? In this article, I’ll explain MySQL’s native options and give you a comparison table of the most common datatypes. We’ll also benchmark some typical queries and reach some conclusions about which datatype to use in a given situation.
Read more
Database internals

All About Indexes Part 2: MySQL Index Structure and Performance

by Emil Drkušić
4 Aug 2016 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.
Read more