Do you wonder if MySQL tells you the truth about writes to tables that have foreign key constraints? The situation is complex, and getting visibility on what is really happening can be a problem.
I found this issue intriguing and decided to share and highlight some examples.
Query Plan
Let us take this example table:
CREATE TABLE `product` ( `category` int NOT NULL, `id` int NOT NULL, `price` decimal(10,0) DEFAULT NULL, PRIMARY KEY (`category`,`id`) ) ENGINE=InnoDB;
We want to know how costly an example UPDATE against this table will be:
mysql > EXPLAIN update product set id=id+1 where id=65032158 and category=3741760\G
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: product
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const,const
rows: 1 …[Read more]
It’s no secret that text or string data are one of the most common datatypes you will process. At times, only a specific portion of a string may be needed and this is the perfect use case for the SUBSTRING() character function…
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
Recently, I published an article over on the OpenLampTech Substack publication page, MySQL SUBSTRING() Function – With Examples, where I cover syntax and examples of the function.
…
[Read more]Ecopaynet's customer success story describes why they chose MySQL HeatWave over AWS to shorten their time to market and benefit from a better price-performance.
For a future article, I was again looking into the possibility of using sysbench to generate data and load on a MySQL database. However, I needed an SSL connection which only version 1.1.0 supports (see issue 308).
Alexey didn’t create a branch and/or release for the version 1.1.0. The code is the master branch.
As you know, I like to install software using the Operating System’s packaging and so I created a rpm for sysbench 1.1.0.
If you are also interested to use this version, here are the rpms for some popular OS and architecture (including Arm):
sysbench-1.1.0-1.fc35.x86_64 …
[Read more]This 20th issue of the OpenLampTech newsletter marks 5 continuous months of weekly publishing. Thank you, the readers, for making it possible and continuing to read these newsletters. Once again, this week’s issue has plenty of PHP/MySQL content so enjoy the newsletter!
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
This week’s OpenLampTech newsletter issue has plenty to read for everyone as I am loading these newsletters up! We are looking at articles covering:
Saturday, I posted how to use Microsoft ODBC DSN to connect to MySQL. Somebody didn’t like the fact that the PowerShell program failed to write a *.csv file to disk because the program used the Write-Host command to write to the content of the query to the console.
I thought that approach was a better as an example. However, it appears that it wasn’t because not everybody knows simple redirection. The original program can transfer the console output to a file, like:
powershell .\MySQLODBC.ps1 > output.csv
So, the first thing you need to do is add a parameter list, like:
param ( [Parameter(Mandatory)][string]$fileName )
Anyway, it’s trivial to demonstrate how to modify the PowerShell program to write to a disk. You should also create a virtual PowerShell drive before writing the file. That’s because you can change the physical directory anytime you want with minimal changes to rest of …
[Read more]Nowadays everything is getting digitalized and migration toward the cloud is at its peak. There is a high chance of data leaks if we don’t tighten the security of the Database servers. It is mandatory to secure the database by restricting access to Database. Although we have restricted the access. We need to monitor the activity of the user to prevent the unwanted usage of data.
Security will be split into three layers
OS level security will be handled by the AWS team. Since It is managed by the AWS Team. So all the security patching, minor version upgrades of OS, and kernel tuning will be governed by the AWS infra team.
Network-level security and database-level security are owned by the end user.
| Security | … |
This post explains and demonstrates how to install, configure, and use the MySQL’s ODBC libraries and a DSN (Data Source Name) to connect your Microsoft PowerShell programs to a locally or remotely installed MySQL database. After you’ve installed the MySQL ODBC library, use Windows search field to find the ODBC Data Sources dialog and run it as administrator.
There are four steps to setup, test, and save your ODBC Data Source Name (DSN) for MySQL. You can click on the images on the right to launch them in a more readable format or simply read the instructions.
MySQL ODBC Setup Steps
If you have worked with an RDBMS for some time, you will likely
have come across the statement SELECT 1.
However, rarely is it correctly explained to engineers what the origin of SELECT 1 is, and why it’s useless and wasteful? A google search is not going to give you the response you would hope, these ranked responses are just as useless as the statement itself.
Bloat
Seeing a SELECT 1 confirms two things. First you are
using a generic ORM framework, quote, and second, you have never optimized your
SQL traffic patterns.
“Frameworks generally suck.
They CLAIM to improve the speed of development and abstract the
need to know SQL.
The REALITY is the undocumented cost to sub-optimal performance,
especially with data persistence.”
Connection Pooling
SELECT 1 comes from …
