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:
Rolls
| Roll Type | Roll Value | Roll Result |
|---|---|---|
| Platform | 3 | SQL Lite |
| Task Category | 2 | Defining Objects (DDL, etc) |
| Task | 2 | Create index |
| Chaos | 1 | Use GUIs in every situation where possible |
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.
