MySQL

Too Long; Didn't ReadAn extensive research, showing how Loose Scan optimisation helps MySQL outperforms PostgreSQL and MS SQL Server on GROUP BY queries for columns with low cardinality. And some advice, what could be done in other database engines to mitigate performance issues. Too Long; Didn't ReadAn extensive research, showing how Loose Scan optimisation helps MySQL outperforms PostgreSQL and MS SQL Server on GROUP BY queries for columns with low cardinality. And some advice, what could be done in other database…

TiCDC is a change data capture (CDC) solution that replicates incremental data changes in real time for TiDB, a distributed SQL database. Previously, TiCDC faced a challenge in its communication with downstream TiDB clusters using query commands, which led to higher CPU usage on SQL parsing and compiling. This issue was particularly significant in larger distributed database systems, where the increased workload could reduce performance and slow down data replication times. Moreover, the lack of plan cache hits further…

A digital marketing analytics exercise with explained step-by-step code in both SQL and Python environmentsPhoto by Zdeněk Macháček on UnsplashAlong this brief journey, we will explore a short and simple dataset with a collection of basic website marketing metrics such as ‘users’, ‘sessions’ and ‘bounces’, over a five months period.The purpose of this setup, rather than focusing on understanding the website performance, is to gain some basic but somehow useful knowledge to answer a number of must-have operational…

Learn how to run SQL in your Jupyter NotebooksPhoto by Wafer WAN on UnsplashTraditionally, data scientists use Jupyter Notebook to pull data from database servers, or from external datasets (such as CSV, JSON files, etc) and store them into Pandas dataframes:All images by author unless otherwise statedThey then use the dataframes for visualization purposes. This approach has a couple of drawbacks:Querying a database server may degrade the performance of the database server, which may not be optimized for analytical…

SaaS applications are the new normal nowadays, and software providers are looking to transform their applications into a Software As a Service application. For this, the only solution is to build a multi-tenant architecture SaaS application. Have you ever wondered how Slack, Salesforce, AWS (Amazon Web Services), and Zendesk can serve multiple organizations? Does each one have its unique and custom cloud software per customer? For example, have you ever noticed that, on Slack, you have your own URL…

Learn advanced MySQL queries to calculate medians on different occasionsImage by geralt in PixabayCalculating the median of an array of data is pretty straightforward in any programming language, even in Excel, where a built-in or third-party median function can be used directly. However, in MySQL, the median function is not natively supported. To get the median, we need to write some smart queries with subqueries.In this post, we will demystify the queries to calculate medians in MySQL. Especially, we will demonstrate…

This article is a comprehensive guide to help you adopt the right type database for your use caseImage by Daniil Silantev on UnsplashConstant changes in data types such as relation and non-relational are one of many challenges faced by companies in their journey of implementing data-driven applications, which consequently makes it harder to choose the right type of database.This conceptual blog will try to make your decision-making process less tedious, with a special focus on MongoDB and MySQL, two of the most popular…

Oracle Corp has started to offer MySQL HeatWave, its cloud database service for transactions, analytics and machine learning, on Amazon's AWS cloud, allowing customers whose data is already parked there to use the service, the US company said. The new offering is not through a partnership with Amazon.com Inc which has its own range of database services that Oracle competes with, said Edward Screven, chief corporate architect of Oracle. He said Oracle does have a partnership with Microsoft Corp and will be offering this…

Add automatically generate data to your tablesPhoto by Hans Reniers on UnsplashMySQL generated columns pose as a powerful, easy-to-use, and advanced tool for anyone who wants to add automatically generated data to their tables — in this blog, we will learn everything you need to know to master them.Generated columns allow you to store automatically generated data in a table without using the INSERT and UPDATE clauses. This useful feature has been part of MySQL since version 5.7, and it represents an alternative approach…

Use change data capture with MSK Connect to sync data between Aurora MySQL and DynamoDBThis is the second part of the blog series which provides a step-by-step walkthrough of data pipelines with Kafka and Kafka Connect. I will be using AWS for demonstration purposes, but the concepts apply to any equivalent options (e.g. running these locally in Docker).Photo by Darya Jum on UnsplashThis part will show Change Data Capture in action that lets you track row-level changes in database tables in response to create, update and…