Behind the slick User Interface (UI) of modern web applications, there are asynchronous services fetching data from the database with a multitude of objectives, including loading drop-downs, populating data tables, Synchronizing components, and many others. Any lagging of the back-end processes will be perceived by the user as a slow or even a non-responsive application. This in turn degrades the user experience and sours their opinion of your application. For that reason, it is imperative that you whittle down your query response time to the lowest feasible value. In many cases, this means measuring query turn-around in hundreds of a second, as opposed to seconds.
Needless to say, achieving sub-second response times takes some doing beyond defining indexes on searchable fields. In today's blog, we'll take a look at some techniques for making your queries maximally performant in MySQL 8.
It's fairly common to allow certain users to perform ad-hoc updates or deletions to tables. Data Manipulation Language (DML) operations such as these always come with risk, and incidents may occur where someone accidentally issues a Delete command without a WHERE clause, thereby deleting all rows in a table! Luckily, there are some simple steps you can take to prevent accidental (or deliberate!) destructive DML operations. We'll examine a couple of these in today's blog.
Certain relational databases, including MySQL and SQL Server, have an INFORMATION_SCHEMA system database. It contains database metadata, such as the names of databases, tables, the column data types, and even access privileges. It's also sometimes referred to as the data dictionary or system catalog. Regardless of how you refer to it, the INFORMATION_SCHEMA database is the ideal place to obtain details about table columns. In today's blog, we'll use the INFORMATION_SCHEMA database to find out whether or not a column exists and how many columns a particular table has.
It's been said that second place is the first loser. So, who needs an SQL statement to find out who these under achievers are? Surprisingly, a lot of people. In fact, the official term for this type of query is "nth highest value of a column". That's because techniques for selecting the 2nd highest value may also be applied for any value. In today's blog, we'll learn how to use ORDER BY DESC in conjunction with the LIMIT clause to obtain the 2nd highest value, and others, from a table.
All-too-often, database developers and administrators use Nulls, Zeroes, and Empty Strings interchangeably within their database tables. That's unfortunate, because Null, Zero, and an Empty String each represent something different in relational databases (RDBMS). As such, using these values incorrectly, or choosing the wrong one, can have enormous ramifications on the operation of your database and applications that rely on it. In today's blog, we'll explore how to best utilize the Null, Zero, and Empty String in database design and general usage.
- 2024 (1)
- 2023 (1)
- 2022 (1)
- 2021 (1)
- 2020 (1)
- 2019 (1)
- 2018 (1)
- 2017 (1)
