How to use SQLite Create Index?

I am using the SQLite plugin for my data needs. In my database, I stored tables containing base64 imagedata and other characteristics (such as album name etc.).

Now I want to optimize my data retrieval using SQLite Indexes, as I noticed that retrieving large amounts of imagedata can take some time.

I am however unsure on which columns to use the Indexes, which type (e.g. do I need Unique Indexes or Composite Indexes) and how the queries are processed? See also description.

My setup is as follows:

Table (Columns)

Masterdata: (id, albumName, favoriteBoolean, dateCreated)
ImagesData: (id, data)
ThumbnailsData: (id, data)

My questions:

• Which type of Index do I have to create to optimize the performance in this example?


• Do I have to create the index only once (when table is created)?


• On which Columns and Tables do I need to apply the CREATE INDEX command?


• Perhaps an explanation of how it works

Anex: Data retrieval

When I for instance want to retrieve all the ImagesData with albumName = "foo", I use the following syntax:

SELECT Masterdata.id, ImagesData.data FROM Masterdata, ImagesData WHERE Masterdata.id = (?) AND ImagesData.id = Masterdata.id

An alternative would be INNER JOIN, but I think that the above does the same (true or not?).