Showing entries 1 to 10 of 12
2 Older Entries »
Displaying posts with tag: Databases & SQL (reset)
MySQL 8.x Adoption Rate – Survey Results

An exciting feature-full new version of MySQL (v8.x) was released on April 2018. Along with those exciting features, there were many changes and enhancements done in MySQL’s internal optimizer. Naturally, we at EverSQL keep an eye on major changes in MySQL’s optimizer, to make sure we are familiar with them and co-exist with them.

So now that 2019 started, I thought it’s a good point in time to look back and see how well this new version is adopted by the community. To do that, we surveyed our users for 3 months, from October to December 2018, asking which MySQL database version are they using for their main product’s environment. Please note that these results are in no way scientifically-true, nor statistically-true. It’s just the data we received and thought it can be interesting to share with the community.

During these 3 months, we received 2000 survey replies from professionals working in companies with more than 5 …

[Read more]
MySQL Explain Example – Explaining MySQL EXPLAIN using StackOverflow data

I personally believe that the best way to deliver a complicated message to an audience, is by using a simple example. So in this post, I chose to demonstrate how to obtain insights from MySQL’s EXPLAIN output, by using a simple SQL query which fetches data from StackOverflow’s publicly available dataset.

The EXPLAIN command provides information about how MySQL executes queries. EXPLAIN can work with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

We’ll first analyze the original query, then attempt to optimize the query and look into the optimized query’s execution plan to see what changed and why.

This is the first article in a series of posts. Each post will walk you through a more advanced SQL query than the previous post, while demonstrating more insights which can be obtained from MySQL’s execution plans.

The query and …

[Read more]
SQL Order of Operations – In Which Order MySQL Executes Queries?

Knowing the bits and bytes of an SQL query’s order of operations can be very valuable, as it can ease the process of writing new queries, while also being very beneficial when trying to optimize an SQL query.

If you’re looking for the short version, this is the logical order of operations, also known as the order of execution, for an SQL query:

  1. FROM, including JOINs
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. WINDOW functions
  6. SELECT
  7. DISTINCT
  8. UNION
  9. ORDER BY
  10. LIMIT and OFFSET

But the reality isn’t that easy nor straight forward. As we said, the SQL standard defines the order of execution for the different SQL query clauses. Said that, modern databases are already challanaging that default order by applying some optimization tricks which might change the actual order of execution, though they must end up …

[Read more]
SQL Performance Tuning Tutorial – MySQL Query Optimization Tips

This is the first part of our SQL Performance Tuning series. In this article, we’ll focus and MySQL related examples, but the same concepts can be applied to many other relational databases.

Now more than ever, software engineers need to have vast knowledge in SQL performance tuning.
The shift is happening in both small startups and large enterprises. Nowadays, developers are the ones writing the SQL queries and database access layer.

As technology advances, even the most novice end-users are becoming impatient and will expect your application to work quickly, even quicker than you’d expect. Therefore, we, as software developers, are bound to meet that endless need for fast and immediate response time, anywhere and anytime.

It doesn’t really matter if you’re using a database abstraction layer (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django, or others) or writing native SQL queries, you’ll eventually …

[Read more]
MariaDB vs MySQL – Comparing MySQL 8.0 with MariaDB 10.3

Introduction

The goal of this article is to evaluate and highlight the main similarities and differences between the MySQL Server Database and the MariaDB Server Database. We’ll look into performance aspects, security, main features, and list all aspects which need to be considered before choosing the right database for your requirements.

Who is using MySQL and MariaDB?

Both MySQL and MariaDB publish a respectful list of customers who are using their database as their core data infrastructure.

For MySQL, we can see names such as Facebook, Github, YouTube, Twitter, PayPal, Nokia, Spotify, Netflix and more.

For MariaDB, we can see names such as Redhat, DBS, Suse, Ubuntu, 1&1, Ingenico and more.

Comparing features – MySQL vs MariaDB

Many new and exciting features like Windows Functions, Roles or Common Table Expressions (CTE) are probably worth mentioning, but won’t be …

[Read more]
5 Easy Ways To Improve Your Database Performance

In many cases, developers, DBAs and data analysts struggle with bad application performance and are feeling quite frustrated when their SQL queries are extremely slow, which can cause the entire database to perform poorly.

Luckily, there is a solution to this problem! In this article, we will briefly cover a few ways you can use to improve the overall database performance. In many cases, you’ll need to use one or more of these paths to resolve database performance issues.

Optimize Queries

In most cases, performance issues are caused by poor SQL queries performance. When trying to optimize those queries, you’ll run into many dilemmas, such as whether to use IN or EXISTS, whether to write a subquery or a join. While you can pay good dime on consulting services, you can also speed up SQL queries using query optimizers such as EverSQL Query …

[Read more]
What’s the best way to learn to write better and faster SQL queries?

Imagine how spider-man’s life would look like if he had never find out that he can shoot webs from his wrist… Not a good starting point, isn’t it?

Sometimes one can learn to write SQL queries but miss critical functionalities and important features, which can change their day-to-day life and save them significant amount of time while writing or reviewing SQL queries.

In the following article, we’ve reviewed and listed some of the best tutorials, hands-on exercises and books which can quickly help you get the knowledge you need to write better and faster SQL queries.

Basic SQL training

  • Khan Acadamy – Khan Academy is a non-profit educational organization created in 2006 with a goal of creating a set of online tools that help educate students. The organization produces short lessons in the form of YouTube videos. Here is a short training with videos and self learning exercise …
[Read more]
Most popular databases in 2018 according to StackOverflow survey

The survey results from StackOverflow’s developers survey are already here, and we can now declare the most popular databases for 2018.

Without further ado, let’s look into the results:

So what can we learn from these results?

  1. The most popular database is MySQL, and not by far comes SQL Server. This wasn’t very surprising, as they were the top two databases last year as well. This year, more than half of the respondents (58.7%) are using MySQL. It seems RDBMS databases and specifically MySQL are here to stay for at least few years ahead.
  2. SQL Server has a large market share as well (41.2%) This is also an increase from last year (30.8%). Microsoft is pushing SQL Server with their Windows Servers, and that way gaining significant market share. In addition, recently Microsoft …
[Read more]
Choosing the best indexes for MySQL query optimization

Many of our users, developers and database administrators, keep asking our team about EverSQL’s indexing recommendations algorithm. So, we decided to write about it.

This tutorial won’t detail all the internals of the algorithm, but rather try to lay down the basic and important aspects of indexing, in simple terms.
Also, and most importantly, we’ll present practical examples for properly indexing your tables and queries by relying on a set of rules, rather than on guessing.

Our focus in this tutorial is on MySQL, MariaDB and Percona Server databases. This information may be relevant for other database vendors as well, but in some cases may not.

Which indexes should I create for an SQL query?

As a general rule of thumb, MySQL can only use one index for each table in the query. Therefore, there is no point in creating more than one index for each query. Preferably, same indexes should match as many of …

[Read more]
How to Transfer a MySQL Database Between Two Servers?

Migrating a MySQL database usually requires only few simple steps, but can take quite some time, depending on the amount of data you would like to migrate.

The following steps will guide through how to export the MySQL database from the old server, secure it, copy it to the new server, import it successfully and make sure the data is there.

Exporting MySQL database to a dump file

Oracle provides a utility named mysqldump which allows to easily export the database structure and data to an SQL dump file. Use the following command:

mysqldump -u root -p --opt [database name] > [database name].sql

Few notes:

[Read more]
Showing entries 1 to 10 of 12
2 Older Entries »