First introduced to MySQL in version 8.013, functional indexes give us the ability to create indexes based on expressions rather than the values of data in a database column. In this post we discuss how to create functional indexes as well as some limitations and implications with their use.

Using a MySQL HeatWave Database Service instance in OCI as an asynchronous replica is very useful. It allows testing the service with always updated data from on-premise production or from another cloud environement. It can be used in the process  of migrating with minimal downtime to OCI and finally, it can also be used between […]

Database indexes are used to help query performance. Database indexes typically contain information about data in a specific column of the database table. With the introduction of functional indexes in MySQL 8.0.13, we can now create indexes based on the result of an expression or function. The Setup Before talking about functional indexes, let’s get some data set up. […]

As a MySQL database administrator, have you ever faced a brute force attack on your database server or been the target of a DDOS-like connection flow on port 3306? If so, you will quickly understand why this plugin distributed with MySQL can be very useful in such situations. Indeed, the connection-control plugin allows the administrator […]

What is MySQL Connection Control and how to use it.

Developers have been storing JSON data in MySQL databases since before the JSON data type existed. With the introduction of functional indexes, developers can now create indexes based on values in data stored in a column with the JSON data type.

Storing JSON in a relational database is something developers have done for quite some time. There are a variety of reasons why you would want to store JSON in a database table – user preferences and configuration data are two good examples. The JSON Data Type was introduced to MySQL in version 5.7.8. This data type allows […]

Looking at how people are using COUNT(*) and COUNT(col), it looks like most of them think they are synonyms and just use what they happen to like, while there is a substantial difference in performance and even query results. Also, we find a difference in execution on InnoDB and MyISAM engines.

NOTE: All tests were applied for MySQL version 8.0.30, and in the background, I ran every query three to five times to make sure that all of them were fully cached in the buffer pool (for InnoDB) or by the filesystem (for MyISAM).

Count function for Innodb engine:

Let’s have look at the following series of examples for InnoDB engine:

CREATE TABLE count_innodb (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  val_with_nulls int(11) default NULL,
  val_no_null int(10) unsigned NOT NULL,
  PRIMARY KEY idx (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

(mysql) > select count(*) from count_innodb; …

Thank you so much for reading OpenLampTech and making it the success it is today. Wow! 600 developers reading each week! I am humbled to say the very least.

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.

In OpenLampTech issue #60, we are looking at some fantastic articles covering:

• Laravel Eloquent and Query Builder tips
• Drupal’s updated CKEditor 5
• How Symfony powers Drupal
• Best SQL Editors
• WooCommerce Payment Gateways
• And much more

Want to help OpenLampTech be a success …

If you have data stored in a Microsoft SQL Server database and you want to import it into MySQL HeatWave Database Service in OCI, you can use the procedure described in this article.