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’.
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. In this article, I’ll explain all you need to know to understand mySQL indexes while at the same time demonstrating how indexing can improve the performance of your queries.
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.k.a. the “partitioning function”). In this way, if the queries you perform access only a fraction of table data and the partitioning function is properly set, there will be less to scan and queries will be faster.
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 whenrepresenting a point in timerather than an absolute duration. Introduction
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.
When you were learning database concepts, data modeling looked pretty easy, didn’t it? You knew all the rules, and modeling seemed like a game: get a challenge, do your best, and eventually solve it. Job well done! Moving up to the next level – and so on.As you continue, though, you’ll see that database modeling is also an art. Many cases require a totally new approach. Everything can be done ‘by the book’, but sometimes you get better results when you go less orthodox.
Storage engines can surprise you. For example, take the CHAR data type. It expects an exact number of characters and by definition stores afixed amountof information. However, you don’t have to fill all the available CHAR space – a shorter value will work. This is so similar to VARCHAR that I decided to explore the differences between these two types.Before diving into the details, let’s start with some basic information. CHAR and VARCHAR are SQL data types dedicated to storing character values. They are available in almost every database engine. Due to database and encoding particulars, the storage of character values in CHAR and VARCHAR columns differs.
Implementing a user-friendly search can be tricky, but it can also be done very efficiently. How do I know this? Not long ago, I needed to implement a search engine on a mobile app. The app was built on the Ionic framework and would connect to a CakePHP 2 backend. The idea was to display results as the user was typing. There were several options for this, but not all of them met my project’s requirements.
In Part 1 of this series, I demonstrated how to install WordPress locally and how to import a WordPress database into Vertabelo . In this article, we’ll take a closer look at the tables in the WordPress database.A Quick Look at the WordPress Database Model and the DashboardIn the previous part , I imported the WordPress database into our online database modeling tool. For the record, the structure of the database is as follows:
How often have you wondered about the structure of an existing database? It could be a 5-year-old legacy system or a brand-new open-source project. Let’s take a look at the ERD diagram of the database behind the most famous content management system: WordPress.What Is WordPress and How Does It Work?WordPress is an open-source CMS (content management system) that was initially released in 2003. It started out as a blogging platform, but it has developed so much that today it can be used for almost anything.
Any database architect designing a MySQL database faces the issue of selecting the proper storage engine. Usually, an application uses only one engine:MyISAMorInnoDB. But let’s try to be a little more flexible and imagine how different storage engines can be used.The Initial Data ModelTo begin, let’s build a simplified data model for a CRM (customer relationship management) system that we’ll use to illustrate the point. The design will cover the main CRM functions: sales data, product definitions, and information for analytics. It won’t contain details typically used in CRM systems.
The General Availability version of MySQL is still version 5.6, but the development release of MySQL 5.7 definitely introduces some exciting changes to the world of database management systems. Is it worth giving a try? In this article, we’ll have a closer look at a few brand-new features that may help you decide to do so.Native JSON SupportJSON (short for Java Script Object Notation) is a format for storing information which can be a good alternative to XML. If you haven’t encountered it already, take a look at a very simple example of a JSON document:
Why Talk About Errors? Model Setup 1 – Using Invalid Names 2 – Insufficient Column Width 3 – Not Indexing Properly 4 – Not Considering Possible Volume or Traffic 5 – Ignoring Time Zones 6 – Missing Audit Trail 7 – Ignoring Collation Why Talk About Errors?The art of designing a good database is like swimming. It is relatively easy to start and difficult to master. If you want to learn to design databases, you should for sure have some theoretic background, like knowledge about database normal forms and transaction isolation levels. But you should also practice as much as possible, because the sad truth is that we learn most… by making errors.
Generating unique integers is a very common task in database systems. Many applications require each row in a given table to hold a unique value. One way to tackle this problem is to use sequences.What are Sequences?A sequence is a database object which allows users to generate unique integer values. The sequence is incremented every time a sequence number is generated. The incrementation occurs even if the transaction rolls back, which may result in gaps between numbers. Similarly, gaps may arise when two users increment the same sequence concurrently.
Back in the day when I was still a PhD student, I helped to organize two scientific conferences that took place in my institute. If you’ve ever been to a big 1000-person developers’ conference, scientific conferences are nothing like them. They usually have around 100 participants; a 300-person conference is considered big. Scientific conferences are organized by the faculty of the university where the event takes place, with some help from interested students or PhD students.
Anyone who had to schedule an intercontinental phone call knows that there is no such thing as a simpletimecallednow. What you should rather think about is a time comprised ofhere and now.The Earth rotates around its own axis. When it’ssolar noon(the sun is at its highest position) in one place, it’s already past noon in places to the east and it’s still before noon in places to the west.To make communication easier, at the end of the 19th century, the Earth was divided into 24 hour-wide
List CollationsTo list all collations available in MySQL, useYou can filter the list to show only collations for charset ‘latin1’:The convention for a collation name in MySQL: first character set name, then the name of the language, finally the type of the collation (ci stands for case-insensitive, cs for case-sensitive, bin for binary collation). So ‘latin1_danish_ci’ is a collation for charset ‘latin1’, for the Danish language and is case-insensitive.Every collation in MySQL is assigned to exactly one character set. Every character set has one default collation which is used if the collation is not specified explicitly. MySQL checks if the collation and the character set match. If they don’t match, an error is raised.
Propel is a popular ORM (Object-Relational Mapping) library for PHP. Recently, the Vertabelo team started to develop VertabeloPropel , a tool which converts your Vertabelo models into Propel’s schema xml.If you want to try using Propel with Vertabelo to develop a simple PHP MySQL application, here is how to do it. The following example uses Composer , a PHP tool for handling project dependencies.Set up Your ProjectCreate the file composer.json which describes your project dependencies. You will use at least Propel (package propel/propel) and VertabeloPropel (vertabelo/vertabelo-propel) as your project dependencies.
SQL has been around for decades and supports a many billion dollar market. However, many people still struggle with just how to pronounce the term SQL. Is it “S.Q.L” [ˈɛs kjuː ˈɛl] or is it “sequel” [ˈsiːkwəl]?SQL… Where it all startedLet’s start at the beginning.Relational databases came into existence with E.F. Codd’s 1970 publication “A Relational Model of Data for Large Shared Data Banks.” While Codd’s ideas were remarkable for the time, in San Jose, California, two colleagues named
In recent times, NoSQL databases have become a hot topic and have gained a crowd of advocates. Indeed, when NoSQL database started to arise, SQL-to-NoSQL converters arose with them as well. But unfortunately, thanks to the code conversion possibility, SQL supporters gained unquestionable evidence that in many cases the good, old SQL is much easier to use. Especially when it comes to queries.I performed a quick Google search and found plenty of nice converter tools. Here is one of them:
The Vertabelo journey continues … We now have almost10,000users and the number of Vertabelo advocates keeps growing strong.Vertabelo users come from over100countries and speak various languages. What unites them?The relational database.Let’s see what relational databases they use:We wanted to determine the most popular database engine among Vertabelo users based on one of three widely-used operating systems:Windows,Linux,Mac OS.Guess what we find out?Among all supported popular databases (PostgreSQL, MySQL, Microsoft SQL Server, SQlite, Oracle, IBM DB2, HSQLDB),
MySQLis the world’s most popular and widely used open-source relational database. It was originally founded and developed in Sweden by two Swedes and a Finn: David Axmark, Allan Larsson andMichael “Monty” Widenius, who had worked together since the 1980’s.Why “My”?The name MySQL owes its name to Monty’s daughter My. Indeed Michael, often called Monty, has a habit of naming his projects after his children: MariaDB was named after his youngest daughter and MaxDB was named after his son Max.
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. This was the kind of lecture I like the most – when you sit and say to yourself “damn, I didn’t know that!”
Generally, we don’t limit query results. However, when we only care about the first few rows or to implement table pagination, limiting query results is just what we need. Database vendors provide us with such functionality; most of them in their own distinct way.ExampleLet’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in theskijump_resultstable. There is no index on theskijump_resultstable. The following queries are examples of a Top-N and pagination query, which I will use in the following parts of the article.
When you create a foreign key in your database, you can specify what happens upon delete of the parent row. There are usually four possibilities:ON DELETE SET NULLON DELETE CASCADEON DELETE NO ACTIONON DELETE RESTRICTToday we’ll investigate the subtle difference between the last two options.In Some Databases There Is No Difference at AllIn Oracle, there is no RESTRICT keyword . The only option is NO ACTION. In MySQL, there is
Sorry for that tabloid title, but I couldn’t resist. I’m going to show you how to view the database design of other people’s systems. It will work for legacy systems as well. Of course, you may call show tables and describe table in the database console but that’s a very rudimentary way to examine the structure. You’ll miss a bird’s eye view of the design which is crucial to understanding a system as a whole.
Suppose we design a database. We’ve created some tables, each one has a few columns. Now we need to choose columns to be primary keys (PK) and make references between tables. And here some inexperienced designers face the dilemma – should a primary key be natural or surrogate?There’s one and only one answer to that question:it depends. If anyone ever tried to convince you that you should have only natural keys or only surrogate keys, just smile :)
How to Develop a PHP/MySQL Web Application for Mobile Customers Using Vertabelo and Other Cloud Services
I’m gonna show you how to develop a simple web application. From database design to deployment and finally some front end development. Everything will be made in the cloud. There will be no need to install anything on your laptop. Sounds like late night TV commercials? But it’s not :).Database DesignLet’s start with the database design:Log in to Vertabelo . Sign up if you don’t have an account yet (you can use a free trial).
group_concat in MySQLMySQL has a very handy function which concatenates strings from a group into one string. For example, let’s take a look at thechildrentable with data about parents’ and children’s names.To get the names of children of each person as a comma-separated string, you use thegroup_concatfunctions as follows:The result:To make sure the names of the children in each string arealphabetically orderedand to use semicolon „;” as aseparator, use this query: