Navicat Blog

Calculating Daily Average Date/Time Intervals in MySQL Mar 19, 2021 by Robert Gravelle

In previous blog, we tabulated the average daily counts for a given column in SQL Server using Navicat for SQL Server. In today's follow-up, we're going to raise the difficulty factor slightly by calculating the daily average date/time interval that is based on start and end date columns. For demonstration purposes, I'll be working with MySQL using Navicat Premium.

Querying Multiple Tables without Joins Mar 15, 2021 by Robert Gravelle

Normally, querying a normalized database necessitates joining tables together on one or more common fields. Otherwise, you risk generating a cartesian product. That is a result set whose number of rows equals those in the first table multiplied by the number of rows in the second table. So, if the input contains 1000 persons and 1000 phone numbers, the result consists of 1,000,000 pairs! Not good. Having said that, if you wanted to aggregate data from similar tables that are not directly related, you can do that using the UNION operator. In today's blog, we'll learn some of the finer points on using UNION, along with its close cousin, UNION ALL.

Three Ways to Perform Bulk Inserts Mar 3, 2021 by Robert Gravelle

I recently wrote a node.js script to iterate over millions of files per day and insert their contents into a MySQL database. Rather than process one record at a time, the script stored file contents in memory and then ran an INSERT statement every 1000 files. To do that, I used the bulk insert form of the INSERT statement. Depending on your particular requirements, you may opt to go with a different solution. In today's blog, we'll go over a few alternatives.

Joins versus Subqueries: Which Is Faster? Feb 18, 2021 by Robert Gravelle

Joins and subqueries are both used to combine data from different tables into a single result set. As such, they share many similarities as well as differences. One key difference is performance. If execution speed is paramount in your business, then you should favor one over the other. Which one? Read on to find out!

Database Optimization: an Overview Jan 22, 2021 by Robert Gravelle

Database optimization is a rather large and sprawling topic that encompasses a multitude of strategies for reducing database system response times. These are often tailored to the specific usage patterns of a database instance or cluster. For instance, in some cases, lightning fast queries might be a goal, whereas for some organizations, faster write times may be what's desired most.

Improving query response times may include activities such as:

  • careful construction of queries
  • use of indexes
  • using analysis tools such as EXPLAIN

In today's blog, we'll learn more about this vital topic in database administration.

Navicat Blogs
Feed Entries
Blog Archives