Creating a database and managing it, if you have about 100 users, isn’t terribly difficult. The trouble comes when the number of users that you have grows exponentially. Suddenly, you realise there needs to be little room for potential error in the design and more importantly you realise the importance of space. Every little counts! Finally, your website’s performance is of paramount priority. If you’re drawing on a ‘student’ table (like we were in one of our applications) and it’s taking 10 seconds instead of 1….something needs to change.

Using indexes can usually help. But they can be incredibly harmful when used incorrectly. (I’ll explain)

Why are we interested in indexing? It’s an easy way of improving performance of your database.

What is an index?

An index is a structure that exists independent of the data in your table that improves the query optimiser’s ability to execute your query quickly.

An analogy- think of the index that you find at the back of a textbook. The analogy is not perfect, but it aids understanding. Say you needed to find every instance of the word ‘moose’ in a very large book about mooses.

You could start at word 1, line 1, page 1 and go through the entire book finding all the instances. Or, you could look up the index, if it exists. You would go to the back of the book and in the index, go to the letter ‘M’, and then find the word. In this magical place, you would be shown every instance of the word ‘moose’ as well as a direct pointer to where the word can be found in every case. It’s efficient and saves you a load of time! Now, imagine if the author decided to add a couple of pages to the middle of the book. What happens to all the index values? That’s right – they’d have to change too. Hopefully this simple analogy helps you see both the advantages and the potential difficulties that can arise with the use of indexes.

The execution in databases is different but the concept is similar.

So, how does indexing work?

Remember an index is just a pointer to the data in a table. It often has to do with ordering by a particular field. This allows more efficient ‘searching (eliminating the need to scan). You could also think of an index as something that represents the book (in our example) in keyword or subject order. The index will basically limit the number of records that we have to look at to satisfy our query. If you have a student table with student information, you could create an index (a separate structure) ordered by ID. Each ID corresponds to data in the student table (a pointer defines this)

The problem however, is that adding or deleting data will affect the index!

Indexes therefore will drastically speed up some queries but can also slow down the insertion and deletion of data. So, generally data retrieval is improved but data modification can be made slower and less efficient.

The three main types of indexes:

Single column, Composite (more than one column is used for the index e.g. lastname and fistname) and Unique (doesn’t allow duplicate values).

So when should you create an index?

1. Only create an index on columns that are frequently used in the WHERE clause.
2. Only create an index on columns that contain a high number of unique values

When should you avoid indexes?

1. If you have a small table! (<8000 rows)
2. If you have column values are not highly unique (e.g. M/F, Y/N etc). There’s no point if half of the values are just ‘M’ and the other are ‘F’.
3. If you have columns that are being frequently updated.
4. If you have large composite indexes.

Sql server has a tool called the ‘index tuning wizard‘ which can be very useful in the building of indexes.

Most functional and large websites have databases. At the most basic level, you may use a database to store information (like username and password) about users.

As a web developer, you have plenty of options for databases. Django comes with SqlLite inbuilt, but this will not be powerful enough if your application takes off and your’e dealing with hundreds of thousands of customers. The good news is that as a beginner, you don’t have to worry about the installation of a database to begin with. You can start working with the inbuilt database right away and simply use the python manage.py makemigrations and python manage.py migrate commands to commit the changes you make in the models to the database. The other most popular options, and the ones you are most likely to come across are MySql and PostgreSQL.

The MySQL software delivers a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. MySQL Server is intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed software.

PostgreSQL is an advanced object-relational database management system that supports an extended subset of the SQL standard, including transactions, foreign keys, subqueries, triggers, user-defined types and functions.

I found this article which provides a nice comparison of all three, with key features, advantages and disadvantages. https://stackshare.io/stackups/mysql-vs-postgresql-vs-sqlite

How to install (say MySql) on your localhost.

Now that you’ve explored the alternatives, you need to know how to actually work with a database on your local host.

Since PHP and MySQL are both server-side languages, you will need a server to practice it.

To make a local host on your computer, you can install either XAMPP or WAMP.

You can download XAMPP here: https://www.apachefriends.org/download.html and another useful tutorial for working with MySql is here: https://www.mysqltutorial.org/install-mysql/