Madelyn  Frami

Madelyn Frami

1596682560

Why I Use TINYINT Columns Instead Of BIT Columns For Boolean Data In A MySQL Application

Since the theme of my week appears to be database interactions (having looked at index structure query-performance on “membership” tables and prepared statements in the JDBC driver), I figured I would end the week on more database-related “hot take”: I no longer use BIT columns to store “Boolean” data in MySQL. Instead, I use a TINYINT column.

CAUTION:_ There is no “right” answer on this topic - this is just my opinion based on my experience and the choices that have come back to haunt me / make my database interactions more challenging._

Semantically speaking, a BIT field is no more clear or meaningful than a TINYINT field when it comes to storing Boolean data. Because, at least in MySQL, **BIT** field isn’t a true-false data type - it’s a binary data type. A BIT field contains “bits”: N-number of bits, between 1 and 64. The only reason that it can represent a true-false value within an application is because the developers working on that application have all agreed to treat it as such.

**TINYINT** field is also not a true-false data type - it’s a numeric data type. And, like the BIT field, the only reason that it can represent a true-false value within an application is because the developers working on that application have all agreed to treat it as such.

So, semantically speaking, neither BIT nor TINYINT are “Booleans”; but, they can both be overloaded to represent a Boolean value if, and only if, the developers working on that application all agree on said social contract.

Now, storage-wise, there’s also no difference between a BIT(1) and a TINYINT because [BIT](https://dev.mysql.com/doc/refman/5.6/en/bit-type.html) field is actually stored in bytes. Meaning, in order to store a single bit, you have to store 8-bits. Which is also the amount of storage required by a [TINYINT](https://dev.mysql.com/doc/refman/5.6/en/integer-types.html).

So, from a physical stand-point, you’re not “saving space” by using a BIT instead of a TINYINT when attempting to represent a Boolean value.

To recap:

  • Neither BIT nor TINYINT is a “Boolean” value. We can only treat them as Boolean values when our developers agree to treat them as Boolean values.

  • Both BIT(1) and TINYINT require a byte of storage.

Since there’s no semantically meaningful difference between the two data-types when it comes to representing Boolean values, neither data-type is “more correct”. But, there are downsides to using a binary field in order to represent a true-false value:

  • The MySQL driver for Node.js returns [BIT](https://www.bennadel.com/blog/3188-casting-bit-fields-to-booleans-using-the-node-js-mysql-driver.htm) data as a [Buffer](https://www.bennadel.com/blog/3188-casting-bit-fields-to-booleans-using-the-node-js-mysql-driver.htm); because, of course it would. After all, a BIT column represents binary data, which is what the Buffer represents in Node.js. As such, in order to translate a BIT(1) result into a true / false data-type, you have add special type-casing logic to your database client configuration.

  • The MySQL driver for Java returns binary data when [BIT](https://www.bennadel.com/blog/2871-using-bit-values-in-coalesce-in-mysql-results-in-binary-values.htm) is used in a [COALESCE()](https://www.bennadel.com/blog/2871-using-bit-values-in-coalesce-in-mysql-results-in-binary-values.htm) call. As such, if you are performing a LEFT OUTER JOIN on a BIT field and attempt to provide a default value with COALESCE(), you have to CAST() the resultant value back to an UNSIGNED type in order to use the value as a Truthy within your application code.

The reason I have articles written on the above two issues is because I’ve personally run into these two issues and wasted hours trying to understand the issue and come up with a work-around.

On the other hand, I have never personally run into an issue when using a TINYINT column to represent a “Boolean” value.

With all that said, I will conceit that MySQL will allow you to store 2 in a TINYINT field; but, will prevent you from storing 2 in a BIT(1) field because a 2 would require more bits than are defined on the column-type. As such, the BIT(1) type does place more constraints on the stored value.

That said, in my ColdFusion applications, if I’m persisting a true / false value, I’m generally not using numeric types in the code - I’m using Boolean types that get persisted as TINYINT types at write-time (using CFQueryParam):

#coldfusion #sql #mysql

What is GEEK

Buddha Community

Why I Use TINYINT Columns Instead Of BIT Columns For Boolean Data In A MySQL Application
Siphiwe  Nair

Siphiwe Nair

1620466520

Your Data Architecture: Simple Best Practices for Your Data Strategy

If you accumulate data on which you base your decision-making as an organization, you should probably think about your data architecture and possible best practices.

If you accumulate data on which you base your decision-making as an organization, you most probably need to think about your data architecture and consider possible best practices. Gaining a competitive edge, remaining customer-centric to the greatest extent possible, and streamlining processes to get on-the-button outcomes can all be traced back to an organization’s capacity to build a future-ready data architecture.

In what follows, we offer a short overview of the overarching capabilities of data architecture. These include user-centricity, elasticity, robustness, and the capacity to ensure the seamless flow of data at all times. Added to these are automation enablement, plus security and data governance considerations. These points from our checklist for what we perceive to be an anticipatory analytics ecosystem.

#big data #data science #big data analytics #data analysis #data architecture #data transformation #data platform #data strategy #cloud data platform #data acquisition

Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

Macey  Kling

Macey Kling

1597579680

Applications Of Data Science On 3D Imagery Data

CVDC 2020, the Computer Vision conference of the year, is scheduled for 13th and 14th of August to bring together the leading experts on Computer Vision from around the world. Organised by the Association of Data Scientists (ADaSCi), the premier global professional body of data science and machine learning professionals, it is a first-of-its-kind virtual conference on Computer Vision.

The second day of the conference started with quite an informative talk on the current pandemic situation. Speaking of talks, the second session “Application of Data Science Algorithms on 3D Imagery Data” was presented by Ramana M, who is the Principal Data Scientist in Analytics at Cyient Ltd.

Ramana talked about one of the most important assets of organisations, data and how the digital world is moving from using 2D data to 3D data for highly accurate information along with realistic user experiences.

The agenda of the talk included an introduction to 3D data, its applications and case studies, 3D data alignment, 3D data for object detection and two general case studies, which are-

  • Industrial metrology for quality assurance.
  • 3d object detection and its volumetric analysis.

This talk discussed the recent advances in 3D data processing, feature extraction methods, object type detection, object segmentation, and object measurements in different body cross-sections. It also covered the 3D imagery concepts, the various algorithms for faster data processing on the GPU environment, and the application of deep learning techniques for object detection and segmentation.

#developers corner #3d data #3d data alignment #applications of data science on 3d imagery data #computer vision #cvdc 2020 #deep learning techniques for 3d data #mesh data #point cloud data #uav data

Gerhard  Brink

Gerhard Brink

1620629020

Getting Started With Data Lakes

Frameworks for Efficient Enterprise Analytics

The opportunities big data offers also come with very real challenges that many organizations are facing today. Often, it’s finding the most cost-effective, scalable way to store and process boundless volumes of data in multiple formats that come from a growing number of sources. Then organizations need the analytical capabilities and flexibility to turn this data into insights that can meet their specific business objectives.

This Refcard dives into how a data lake helps tackle these challenges at both ends — from its enhanced architecture that’s designed for efficient data ingestion, storage, and management to its advanced analytics functionality and performance flexibility. You’ll also explore key benefits and common use cases.

Introduction

As technology continues to evolve with new data sources, such as IoT sensors and social media churning out large volumes of data, there has never been a better time to discuss the possibilities and challenges of managing such data for varying analytical insights. In this Refcard, we dig deep into how data lakes solve the problem of storing and processing enormous amounts of data. While doing so, we also explore the benefits of data lakes, their use cases, and how they differ from data warehouses (DWHs).


This is a preview of the Getting Started With Data Lakes Refcard. To read the entire Refcard, please download the PDF from the link above.

#big data #data analytics #data analysis #business analytics #data warehouse #data storage #data lake #data lake architecture #data lake governance #data lake management

Madelyn  Frami

Madelyn Frami

1596682560

Why I Use TINYINT Columns Instead Of BIT Columns For Boolean Data In A MySQL Application

Since the theme of my week appears to be database interactions (having looked at index structure query-performance on “membership” tables and prepared statements in the JDBC driver), I figured I would end the week on more database-related “hot take”: I no longer use BIT columns to store “Boolean” data in MySQL. Instead, I use a TINYINT column.

CAUTION:_ There is no “right” answer on this topic - this is just my opinion based on my experience and the choices that have come back to haunt me / make my database interactions more challenging._

Semantically speaking, a BIT field is no more clear or meaningful than a TINYINT field when it comes to storing Boolean data. Because, at least in MySQL, **BIT** field isn’t a true-false data type - it’s a binary data type. A BIT field contains “bits”: N-number of bits, between 1 and 64. The only reason that it can represent a true-false value within an application is because the developers working on that application have all agreed to treat it as such.

**TINYINT** field is also not a true-false data type - it’s a numeric data type. And, like the BIT field, the only reason that it can represent a true-false value within an application is because the developers working on that application have all agreed to treat it as such.

So, semantically speaking, neither BIT nor TINYINT are “Booleans”; but, they can both be overloaded to represent a Boolean value if, and only if, the developers working on that application all agree on said social contract.

Now, storage-wise, there’s also no difference between a BIT(1) and a TINYINT because [BIT](https://dev.mysql.com/doc/refman/5.6/en/bit-type.html) field is actually stored in bytes. Meaning, in order to store a single bit, you have to store 8-bits. Which is also the amount of storage required by a [TINYINT](https://dev.mysql.com/doc/refman/5.6/en/integer-types.html).

So, from a physical stand-point, you’re not “saving space” by using a BIT instead of a TINYINT when attempting to represent a Boolean value.

To recap:

  • Neither BIT nor TINYINT is a “Boolean” value. We can only treat them as Boolean values when our developers agree to treat them as Boolean values.

  • Both BIT(1) and TINYINT require a byte of storage.

Since there’s no semantically meaningful difference between the two data-types when it comes to representing Boolean values, neither data-type is “more correct”. But, there are downsides to using a binary field in order to represent a true-false value:

  • The MySQL driver for Node.js returns [BIT](https://www.bennadel.com/blog/3188-casting-bit-fields-to-booleans-using-the-node-js-mysql-driver.htm) data as a [Buffer](https://www.bennadel.com/blog/3188-casting-bit-fields-to-booleans-using-the-node-js-mysql-driver.htm); because, of course it would. After all, a BIT column represents binary data, which is what the Buffer represents in Node.js. As such, in order to translate a BIT(1) result into a true / false data-type, you have add special type-casing logic to your database client configuration.

  • The MySQL driver for Java returns binary data when [BIT](https://www.bennadel.com/blog/2871-using-bit-values-in-coalesce-in-mysql-results-in-binary-values.htm) is used in a [COALESCE()](https://www.bennadel.com/blog/2871-using-bit-values-in-coalesce-in-mysql-results-in-binary-values.htm) call. As such, if you are performing a LEFT OUTER JOIN on a BIT field and attempt to provide a default value with COALESCE(), you have to CAST() the resultant value back to an UNSIGNED type in order to use the value as a Truthy within your application code.

The reason I have articles written on the above two issues is because I’ve personally run into these two issues and wasted hours trying to understand the issue and come up with a work-around.

On the other hand, I have never personally run into an issue when using a TINYINT column to represent a “Boolean” value.

With all that said, I will conceit that MySQL will allow you to store 2 in a TINYINT field; but, will prevent you from storing 2 in a BIT(1) field because a 2 would require more bits than are defined on the column-type. As such, the BIT(1) type does place more constraints on the stored value.

That said, in my ColdFusion applications, if I’m persisting a true / false value, I’m generally not using numeric types in the code - I’m using Boolean types that get persisted as TINYINT types at write-time (using CFQueryParam):

#coldfusion #sql #mysql