DEV Community: Ngozi Kalu

Benefits of indexing in building fast-performing APIs

Ngozi Kalu — Thu, 27 May 2021 09:47:24 +0000

I was opportune to present this topic at engineering a few weeks ago, so I decided to share it on my blog as well. Database indexing is a huge subject, but here I have streamlined it to fit how we can have fast performing APIs with the right indexes.

What is a Database Index?

An index is a special lookup table that aids faster table searches, it also aids faster updates and deletes by a database engine.

An index in a database is similar to an index at the back of a book. for example, if we want to locate a topic called “Operating Systems”, let’s say this topic is on page 78 out of 100 pages in the book, without an index, chances are that we have to flip through each page of the book up till the 78th page before we can find the topic “Operating Systems”. Even if we do not flip through each page, we will keep turning pages until we locate the topic, you agree this is stressful and wastes time right? but with an index, we will not flip through 77 pages to find the topic, all we need to do is go to the index of the book, locate the topic and its page number, then go back to the book and specifically get to that page number. Now you agree that this is a faster searching approach right? It is exactly the same thing that happens when we place an index on a database column.

As an example, if we have a database table called Products that has 4 columns: Id, Name, Price, Quantity, let’s say this table has 40,000 records and we want to locate all the records that have Price as 15,000.

Select * from Products where price = 15000

When we run the above query without an index, the database engine will return the records for us, right? But what the database engine has done under the hood is scan through 40,000 records and filter out the records that have their Price as 15,000, before returning them to us, This is called a table scan and table scan is bad performance. Imagine that the table has millions of records and the database has to do this table scan, it will take quite some time, and if we are loading this query from our application UI, it will pose a bad user experience because the page that calls the API would delay before it loads the data. With an index on the Price column, the database engine creates another special lookup table where it saves Prices with a row address of each price, such that when we run the above query, the database engine goes to that lookup table and retrieves the row address of that price, then uses the row address to fetch the records from the actual table, If there are 3 records with Price of 15,000, the lookup table will have those 3 records with their unique row addresses, it will start from the first occurrence, and then scan downwards to fetch all row addresses that match the criteria. Let’s illustrate this example.

Products Table:

Index:

Resulting records:

When To Use Index?
While creating an index, bear in mind that updating a table with indexes takes more time than updating a table without because the indexes also need an update. It’s important to only create indexes when it is needed and absolutely necessary. The next question on your mind would be when is it absolutely necessary? The following are some valid reasons to create an index on a column:

When the column is queried frequently, by this it means when the column often appears in where clauses, or when the query that this column appears in is run very frequently, then the column should be indexed.
Secondly, if a referential integrity constraint exists on the column, then the column needs an index on it, which means columns that would partake in joins in your query require an index.
If a UNIQUE key integrity constraint exists on the column, then the column needs a unique index on it. A unique index is a way of applying a unique key constraint on a column, the database engine would create the special lookup table (index) as well as enforce uniqueness of values in that column.
When To Not Use Index?
Indexes are not needed in all scenarios and too many indexes are also not a good practice as this can cause issues, Some scenarios where we don’t need to add an index:

a. When the table is a small table for example a table holding records that don’t increase at all or don’t increase significantly, e.g a table that holds days in the week, months in the year, public holidays in a year, cities in a state. (some of these can be enums instead of tables anyway). These are small tables, so even if any column in these type of tables appears frequently in where clauses, there is still no need for an index, this is because indexes take up storage, and not indexing these columns will not hurt performance, since the table scan would be done on a minimal number of records.

b. Another scenario to not index is when there are low Reads and high Writes on the table, this means when the table is not often searched or queried but it is often updated, it will not hurt performance if no index is placed on the columns, regardless of the table size.

c. When we are often pulling a great percentage of the database records and not a small percentage. You don’t want to place an index on a column if, in your search criteria, it’s going to pull almost all the records in the table. This point may be debatable but check out the query below;

Select * from Employees where id < 10000

Assuming id increments by 1.

If the number of records is maybe 11,000 or even 12,000 and if this is all the query you need to run on this table, then there is no need to index because the database engine would have to read the index heavily to fetch all the records which is poor performance for the database. It will not hurt to do a table scan on 12,000 records if the expected result is 10,000 records.

What Columns Should I Index?

a. Index should be placed on columns that often appear in where clauses and order by clauses.

b. A column that has a unique constraint should have a unique index.

c. A column that is a foreign key needs an index.

Single or Composite indexes:

A single index is an index on one column and a composite index is an index on multiple columns.

When to use both?

We use a composite index when multiple columns are frequently being searched against and a single index when only one column is frequently being searched against. When creating a composite index, we need to specify the order of the index in each column, this order is used by the database engine to determine which column comes first, second, third.. in the special lookup table it is going to create, and helps us in determining how the where clause of the query should be structured. It is compulsory to order our columns in the where clause according to the same way the database engine orders the columns in the lookup table.

Understanding how to order indexes is very important in other not to waste storage space and memory in creating unnecessary or unused indexes, one may create an index on a column, that may appear in Where clauses, still the query may not benefit from the index. An example:

If you add an index on City and Country columns, if the index order of Country is 0 and City is 1, then the order of the columns in the where clause of a select, update or delete query on that table should be:

Select FieldName1, FieldName2… from TableName where Country = “value” and City= “value”

The following query will not benefit from that index, and in fact, that will be a waste of index, storage, and memory if the query was structured in this manner.

Select FieldName1, FieldName2… from TableName where City= “value” and Country = “value”

In the same manner, an index in the order Country (0), State(1), City(2) will benefit the following query

Select FieldName1, FieldName2… from TableName where Country = “value” and State = “value” and City = “value”

Select FieldName1, FieldName2… from TableName where Country = “value” and State = “value”

Select FieldName1, FieldName2… from TableName where Country = “value”

But it will not benefit the following query.

Select FieldName1, FieldName2… from TableName where State = “value” and Country = “value”

The reason is that column ordering matters when creating composite indexes, the column that has the least order number must always come first in the where clause, it should follow that order to the column with the highest order number, which means that City with column order 2 should come last in the where.

Also, with the above, keep in mind that there are different types of indexes applied on columns depending on what you want to achieve such as Clustered and Non-Clustered index, Filtered index, Unique index (I mentioned it in this article), etc.

A Guide Into Programming For Newbies

Ngozi Kalu — Tue, 28 Jul 2020 07:53:31 +0000

If you are seeing this page, I want to believe that you are either new to tech or new to programming, and in need of a guide to help you make a choice of what to learn in other to be what you want to be in the tech world. This article mainly aims to direct you in choosing a path in the case where you don’t know what path to go, and also present you with several options to choose from.

First, If you are not sure of a path to take, there are several things you may want to consider in your decision of choosing a path, some of the personal questions you need to ask yourself are;
What really interests me in tech, or what am I originally skilled at? (The answer to this question should be your cravings, do you have interest in beautiful designs, or are you fascinated with data or its analysis, or is it functionality and behavior that excites you? etc)

How long would it take me to learn the technology for my area of interest? (By technology, I mean a programming language or a framework. Don’t worry if you don’t understand what a framework is, in the course of reading this article, you will get a grasp of it.)
What is the job market like? How much are recruiters needing this technology?
How efficiently or effortlessly can I use this technology?
What is the community like? (By community, I mean the number of people currently using this technology)
How old or how recent is this technology? For the most part, if you can find answers to the above questions, it will greatly influence your decision in making a choice. Tips to choosing a path based on your area of interest; If you have a graphic design background, an interest in digital products then becoming a UX or UI designer might be your go-to path.
If you have a thing for the databases? You may want to take a deep dive into the world of databases and become a database administrator.
If you like to analyze trends in data, then definitely, you may want to go into data science.
If you like to implement functionality, or often curious about what goes under the hood in any web application, then you can explore web development (Backend).
If you like to design web pages, then you can explore web app development (Frontend).
If being part of a team that builds apps that you can run on your mobile device excites you, then mobile app development should be your go-to path.

Secondly, assuming you have chosen a path, but you don’t know what programming language to learn, In an age where there are hundreds of programming languages, it is normal to have difficulty in making this choice of a language, Once you choose a language and learn it extensively, it will no longer be difficult to adapt other technologies.

Now, you should have an idea of what your go to path should look like.
If you think you have an interest in Web Application Development, please read further:
There are two sides to any web application, you can focus on either of them;

Frontend development
Backend development

Frontend Development:

Frontend is like the exterior of a house, or a vehicle, or anything, that displays the beautiful interface that someone can visualize and interact with.

Frontend development involves developing what the user sees and interacts with. Also, the Frontend does not have any business with interacting with databases. In other news, a Frontend developer tranforms a UI/UX design into code(again, don’t worry if you don’t yet understand what UI/UX means).
What do I need to be a front end developer?
The basic skills needed for being a Frontend developer are:
HTML (Required)
CSS (Required)
JavaScript (Required)
Frameworks (Optional)

1) HTML:
HTML is the markup language that defines the structure of a web page, note that HTML is not a programming language. In layman’s terms, HTML is what you use to display texts, images, videos etc on a web page, it will define the layout of the page. It is impossible to make a website without using HTML. As an example, you can create a button with the text “Click to see Hello World” and display it on your browser. HTML when likened to a human body, is like the skeleton of the human body, HTML works together with CSS, without CSS, Your web page will be the ugliest ever, just like the image below.

2) CSS:
CSS is a style sheet language that is used to describe how HTML elements are to be presented on the page. Like in the example above, CSS can be used to change the color of the button to blue. CSS when likened to the human body, is like the flesh of the human body, that gives beauty to the skeleton. The button below was styled using CSS.

3) JavaScript:
JavaScript is what provides functionality that your HTML page uses. JavaScript determines the behavior of your web page. In the example above, JavaScript is used to control the behavior of the button, such as when the button is clicked, a message can pop up. JavaScript when likened to human, is like the mechanics that control human movement or any other behavior. In the following image, a script like JavaScript would trigger a message to pop up on the window when the button is clicked.

HTML, CSS and JavaScript are the required technologies needed to become a Frontend developer. When learning these technologies, learn them in this order HTML => CSS => JavaScript.
After learning these, you can go further to learn a Framework.

What is a Framework?

The picture above is the idea of a Framework. It gives you a structure to fix something, in this case your code. A framework will control the design of your application. It will force you to work in a standard way. For instance, a particular framework will force you to code your login functionality in a particular way, or make an HTTP request in a particular way(an HTTP request is just like you trying to access google.com from your application), while another framework will force you to do these things in another way. Also, a framework can consist of libraries that you have to use. (don’t know what a library is, not to worry, read on).

Why should I bother learning a framework if it’s optional then?

While it is not compulsory to learn any framework, it is also important you do. The reason is that frameworks will make writing code faster and more standardized. Also, most companies adopt the use of certain frameworks to aid their development, and trust you want to have the skill that your favorite company needs.

What HTML Framework should I learn?

There are several HTML frameworks that I will not cover in this article, you can explore them if you like, but more often than not, the basic HTML is enough for any web page you want to build.

What CSS Framework should I learn?

For a start, Bootstrap is a good place. This is because Bootstrap is widely and largely used. There are over 15 CSS frameworks that exist. Bootstrap is the most popular CSS Framework, and as a newbie, starting out with this framework would be a good decision.

Note: Learning frameworks without mastering the basics would leave you struggling to understand it, and also jumping from framework to framework, because few of these frameworks stay for a long time and a newer one comes out to displace them.

What JavaScript Framework should I learn?

Popular JavaScript Frameworks you can choose to learn:

Angular: Angular is used to develop single-page applications.
Vue: Vue can also be used to develop a single-page application. Other JavaScript frameworks you can explore are ember, nextjs, Svelte, Backbone etc.

What is a Library?

A library is a bunch of reusable code that you can include inside your program. A common-sense explanation is this, suppose you have a bunch of code that you write every time in different parts of your application, you can write this code once and save it in a file, then call it every time you need it. So as a programmer, you can write and use your own libraries and you can also use external libraries (libraries written by others).

What JavaScript Library should I learn?

You can use any of your choice but the following JavaScript libraries are largely used;

React JS: Currently, this is largely used than other libraries.
jQuery: Usage of jQuery has reduced over time owing to the advent of so many other libraries that offer better capabilities. Regardless, jQuery is so easy and so powerful and can still be used alongside other frameworks.

Backend Development:

Backend is like a vehicle engine, it’s not always seen but it’s the power house of the vehicle.

What do you think happens when you fill a form online and submit it? Whatever happens behind the scene that you cannot see is powered by Backend development. So a Backend developer is one who codes for the Backend. For the most part, there is usually a database interaction in Backend development, so while learning any Backend technology, have it at the back of your mind that you need to store your data somewhere, which means you need to have basic understanding of interacting with a database.

What do I need to be a Backend developer?

You can learn any (just one) of the following technologies/Languages:

Python with Django Framework or Flask Framework: For building Backend for web applications, you have to learn Python with either the Django framework or the flask framework. Its database Support include PostgreSQL, MariaDb, MySQL, Oracle, SQLite.
Ruby: The good thing about ruby is that it aids faster development of web applications However, Ruby on Rails is losing its popularity, Ruby does not have a huge community. Some supported databases are SQLite, PostgreSQL, MySQL.
PHP: PHP is suitable for making powerful and dynamic web pages. PHP supports MySQL, Oracle, PostgreSQL, Sybase, IBM-DB2 databases.
JAVA: JAVA has a huge community of developers. Over tens of millions of developers. JAVA has support for JAVA DB, MySQL, Oracle, PostgreSQL, SQL Server, Sybase, DB2 databases.
C#: With frameworks like ASP.NET MVC or .NET Core, one can develop applications for the Backend, .NET supports SQL Server, MySQL etc.
C++: C++ can code for any platform. But it is not so easy to learn. C++ supports SQL Server, Oracle, Sybase, MySQL, PostgreSQL databases.
NodeJs: node is a runtime environment for running JavaScript code. note that node requires JavaScript, so you must know JavaScript before node. Node supports several databases like; MongoDB, Redis, CouchDB, SQL Server, MySQL, PostgreSQL, Oracle, SQLite.

Do you wish to develop applications that run on mobile devices? If yes, then you surely want to be a mobile developer. So read further.

A mobile application is an application that is designed to run on only mobile devices such as smartphones and tablets etc, most technologies used for Backend are also used for mobile development. Likewise, it requires a database to store data and also a programming language.
What frameworks or technologies do I need to be a mobile app developer?
Learn how to use any of the technologies listed below:

Xamarin Framework: It is used with c# language, It supports SQLite and Realm database.
Flutter SDK: Dart language is used with flutter. It supports SQLite database.
React Native: It is used with JavaScript, It supports Firebase, SQLite, Realm, PouchDb databases.
Ionic Framework: It is used with Angular JS, It supports SQLite, Firebase.
Cordova-Apache: It uses HTML, CSS, and JavaScript. It supports SQLite.
Java: It is used with JAVA.It supports SQLite, Realm.
Objective C: This is the primary programming language for developing iOS apps. It is gradually been replaced by Swift. The language used is C. It can support SQLite, Realm
Swift: Swift is used to develop for iOS, macOS, watchOS, tvOS, and Linux applications. It can support SQLite and Realm DB.
Kotlin: Kotlin is built around JAVA, but it offers big advantages over JAVA, one of which is that it can code cross-platform. It can play nicely with JAVA in the same project. It can support SQLite, Realm, Firebase etc.

Do you wish to be a UI/UX designer?

UI and UX are different skills, but go hand in hand with each other.

UX (User Experience):

Generally, User Experience focuses on how something works and how people can interact with it. a UX designer should ensure that a product, be it software or anything, makes sense to the end-user. Think of how easy it is for you to set up and use an app like WhatsApp and also how interesting the app is to the user, a first time user may need little or no help to use it. How easy it is to navigate your Android device? How many clicks does a user have to do in other to achieve a particular thing on your software? It is called User Experience. The ability to put yourself in the user’s shoes is the first required skill you should have as a UX designer.

UI (User Interface):

User Interface focuses on the look and layout of a design. The UI designer does the graphics of the product and should have a sound knowledge of graphics to make a good UI designer. A UI designer is the one who creates graphic designs called mock-ups and gives it to a Frontend developer, the Frontend developer will transfer it into code and run it on a web browser.

What tools do I need to be a UI/UX designer?

UI/UX designers use a range of tools within their reach; examples are
Photoshop, Adobe XD, Sketch, Illustrator, Figma, MockFlow etc.

Do you want to be a Database Administrator?

In simple terms, a database administrator is one who ensures that everything is put in place to ensure the availability of the database to its users. The most popular language for manipulating databases is called Structured Query Language(SQL), understanding this language is key for most databases.
There are several databases and database management systems that exist,
Oracle, MySQL, Microsoft SQL, PostgreSQL, DB2, Microsoft Access, SQLite, Redis.

Do you want to be a Data Scientist?

You may have heard that the future is data, that is absolutely correct. Every company has and uses data. Ability to use existing data to extract insights and knowledge which is beneficial to anyone is what makes you a data scientist. In 2020, the demand for data scientists is increasing at a geometric progression.
What do I need to be a Data Scientist?

Learn how to use Python
R
SQL (Structured Query Language)
Basic Statistics: your basic mean, mode, median etc.

There are several other options in tech you may be interested in but were not covered in this article, Example of other areas include Machine Learning, IOT, Natural Language Processing, Ethical Hacking, Robotics etc. The end goal is to do what makes you happy while you add value to people, and earn money in return.
Choose a path today and start learning all the way until you can create solutions to a person’s need.