MySQL has introduced the concept of functional index in MySQL 8.0.13. It is one of the much needed feature for query optimisation , we have seen about histogram in my last blog. Let us explore the functional index and its use cases.

For the below explanation, I have used a production scenario which has 16 core cpu, 32GB RAM and with MySQL version 8.0.16(Latest at the time of writing).

MySQL do support indexing on columns or prefixes of column values (length).

Example: 

mysql>show create table app_user\G
*************************** 1. row ***************************
Table: app_user
Create Table: CREATE TABLE `app_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ad_id` int(11) DEFAULT NULL,
`source` varchar(32) DEFAULT NULL,
`medium` varchar(32) DEFAULT NULL,
`campaign` varchar(32) DEFAULT NULL,
`timestamp` …

We bring you the best MySQL tutorial to learn all Basic to Advanced concepts step by step. This post covers all MySQL building blocks such as DDL, DML, DCL, and TCL. DDLs are commands to create, drop, alter, truncate, rename, comment databases, and tables. Also, DMLs are select, insert, update, delete commands to manipulate data. Moreover, you’ll also find the DCLs on this page such as grant and invoke to manage rights and permissions. After that, there are TCLs to control the transactions. So, let’s start by telling you that the father of MySQL is Michael Widenius, who named it

The post MySQL Tutorial – Learn Step by Step appeared first on Learn Programming and Software Testing.

Quickly configure replication using DBdeployer [SandBox]
We might have different scenarios when we need a quick setup of replication either between the same version of MySQL (Like 8.0 --> 8.0) or between the different version of MySQL (Like 5.7 --> 8.0) to perform some testings. 

Here in this blog post, I will explain how we can create our replication lab setup quickly using the virtual machine and DBdeployer tool. 

Let's see, how to create replication between the same version and different version of MySQL using DBdeployer step by step. 

Create CentOS VM Please find my …

What are things you must consider before using Galera Cluster Streaming Replication ? 

In Streaming Replication, the node breaks the transaction into fragments, then certifies and replicates them on the slaves while the transaction is still in progress. Once certified, the fragment can no longer be aborted by conflicting transactions. Additionally, Streaming Replication allows the node to process transaction write-sets greater than 2Gb. So, How do you decide whether go with Galera Cluster Streaming Replication or not ? We caution our customers before choosing Galera Cluster Streaming Replication considering several limitations and costs attached to it (explained below the limitations of Galera Cluster Streaming Replication in this post). Even you have decided to proceed with Galera Cluster Streaming Replication, We recommend you to enable it only for a session-level and then only on specific transactions.

…

Recently we had been bitten by a Systemd limitation at the “Tasks” created per-unit ie., process. This includes both the kernel threads and user-space threads, with each thread counting individually.

Am writing this blog as a reference for someone who might come across this limitation.

We have been actively working on migration DB instances, from one DC to the newly built DC .The instances on the newer DC were provisioned with the latest hardware and latest Debian OS. Below is the detailed spec of the system.

RAM             : 244G
Core             : 44Core
HardDisk.   : SSD
IOPS             : 120K
OS         …

This laravel tutorial help to add CRUD operation with datatable, I will just share laravel code to create new record, edit record and delete record. I have already shared How To Use jQuery Datatable With Laravel.We have integrated jQuery datatable with laravel. We will cover following functionality in this laravel tutorial : Create record Template […]

The post Laravel CRUD Example with jQuery Datatable appeared first on Phpflow.com.

Congratulations to Team MariaDB at MariaDB Corporation and MariaDB Foundation for releasing MariaDB 10.4.6 as Generally Available (GA) last week on 18 June 2019. This release is very exciting for Galera Cluster users as it comes with Galera 4 (it is now the first server to come with it!), with Galera wsrep library version 26.4.2.

What can Galera Cluster users expect from MariaDB 10.4? Some high level features include:

• Streaming replication — a huge boost to large transaction support, since the node breaks transactions into fragments, replicates and certifies it across all secondary nodes while the transaction is still in progress. Read more about it in our dedicated documentation on streaming replication as well …

This is the first in a series of blog articles in which I will discuss the changing landscape of open source and money. Or, more specifically, open source databases and money. And even more specifically MySQL and its all variants (AWS Aurora, MariaDB, Percona Server, RDS/MySQL) and money. But before going too deep into what is changing, let’s review all the traditional business models in and around the MySQL marketplace.

In general, these are the following types of companies in the MySQL commercial ecosystem, sorted by total annual revenue and addressable market size:

• Developers who do not aim to monetize the open source code, just provide value to others and hope to get development and other contributions in return. This is the purest form of open source. For example, all …

MySQL supports different types of Indexes. They depend on the storage engine and on the type of data. This is the list of supported indexes:

• B-trees (the most common)
• R-trees (for spatial data)
• Hashes (for Memory engine)
• Inverted (for InnoDB Full Text)

Best Practices

My recommendation are valid for InnoDB storage engine. I won’t talk about MyISAM. There are some best practices to follow when designing your tables. These are the 3 most important:

In my previous blog post, I explained the internals of the sorted index build process. The blog ended with saying “there is one disadvantage.”

Beginning in MySQL 5.6, many DDLs including ALTER TABLE ADD INDEX became “ONLINE”. Meaning, when the ALTER is in progress, there can be concurrent SELECTS and DMLs. See the MySQL documentation for online DDL. From the documentation, we can see that ALTER TABLE ADD INDEX DDL permits concurrent DML.

The main disadvantage with Sorted Index Builds introduced in 5.7 is the reduced insert performance when ALTER is in progress. In this blog, we specifically talk about single thread insert performance on a table with ALTER ADD INDEX in progress.

If the table …