Roll For Database, Episode 2 … SQL Lite: Create an Index

I had the idea of recording my rolls to prove that I’m taking whatever the the SQL random function gives me. Here’s my first attempt at that:

Video showing roll for database rolls for this article

Rolls

Roll TypeRoll ValueRoll Result
Platform3SQL Lite
Task Category2Defining Objects (DDL, etc)
Task2Create index
Chaos1Use GUIs in every situation where possible
Table listing each individual roll for this episode

Other than experimentation, I don’t have much experience with SQLite, and the experience I do have is mostly in combination with python in a Jupyter Notebook. As far as I can tell, there isn’t a native GUI for SQLite. I’d love to hear in the comments below if I’m wrong here. My first choice for a platform-agnostic GUI is the community edition of DBeaver, so I’ll download DBeaver from https://dbeaver.io/download/ and try it. I like this because it is free, and from days when I used it for my day job, I know that it’s fairly fully featured for a free option.

The sample database that I’m aware of for SQLite is the Chinook database. Simply download it from that page, and you can then open it from the sqlite3 command line or from a GUI.

Action

First, I need to get my bearings in this sample database. My goal is to create an index, using a GUI. While I tend to default to SQL-first, the GUI is actually really easy to use to find a table and understand the structure of it. I used the left pane to expand the list of tables under the chinook database, clicked on the employees table, and then click on columns. I also clicked on indexes here to get an idea of the indexes already in existence on the table:

Using the GUI as much as possible requires me to right click on indexes in the left pane and select “Create New Index”.

This brings up a pop-up where I can select the column or columns to include in the index. I’ll select LastName.

When I click OK, the GUI creates the index for me, and even gives it a name.

The name it chose is not unreasonable. It does make me wonder if I can set a naming standard for the index names in DBeaver.

What I Learned

I must admit to a bit of database snobbery against SQLite when I started on this task. My first question was “does SQLite even have indexes?”. I learned that not only does SQLite have indexes, but a bit of quick googling tells me that they do indeed improve query performance. I have a hard time thinking of an entire database as a single file at all, much less using different structures within that file to help performance. Many of the database management systems I have worked with use files in very specific manners, and dozens, hundreds, or thousands of files.

I also learned that when using the GUI for a task like this, I’m showing the capabilties of the GUI as much as those of the RDBMS for simple tasks. I am still a command-line gal at heart.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 557

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.