Чрезвычайно медленные простые запросы SQLite с внешнего диска

У меня есть база данных SQLite на внешнем жестком диске, и я хотел бы проанализировать пару тысяч ее подмножеств в R (или Python). Данные представляют собой около 110 миллионов наблюдений за 13 переменными и хранятся (~ 10,5 ГБ) на внешнем диске, потому что на моем компьютере недостаточно места для хранения. Я решил попробовать использовать базу данных SQL, потому что, хотя у меня недостаточно оперативной памяти для работы со всем набором данных в R, мне нужен доступ только к небольшому его проценту в любой момент времени.

Я построил базу данных с помощью RSQLite — по сути, просто добавив несколько фреймов данных с помощью dbWriteTable() — и могу успешно запросить ее. Проблема в том, что даже очень простой запрос к базе данных занимает несколько минут. Например, это займет около 3 минут (либо в R, либо с sqlite3 в Python):

# example in R
library(RSQLite)

# connect to existing database
conn <- dbConnect(RSQLite::SQLite(), EEGdata.db)

# example of simple query
testQuery <- dbSendQuery(conn, "SELECT * FROM Data WHERE Time = 42")

# return data (takes "forever" but does work)
testdf <- dbFetch(testQuery)

Я пробовал несколько разных (но я думаю, эквивалентных) способов выполнения запроса, например, с помощью dbplyr, но все они одинаково выполняются за несколько минут.

Я пробовал использовать транзакции (но я так понимаю, это в основном просто помогает вставлять или изменять информацию?).

Я пытался использовать индекс, который ускоряет запросы, но их выполнение все равно занимает больше минуты.

Я пытался использовать последние версии R/RSQLite, как рекомендовано в: Запрос SQLilte через RSqlite намного медленнее, чем через интерфейс командной строки sqlite3

Я проверил, что общая скорость чтения/записи моего внешнего диска (WD, мой паспорт) не слишком низкая (он передает данные со скоростью около 100 Мбит/с).

Я также пытался использовать sqlite3 через Python и получаю примерно такие же очень медленные запросы.

Я предполагаю, что я мог сделать базу данных как-то неправильно, но непонятно, почему она все еще работает, просто очень плохо. На всякий случай, это пример того, как я создал базу данных:

library(RSQLite)

# open connection to new database
conn <- dbConnect(RSQLite::SQLite(), EEGdata.db)

# loop over data files
for (file in filenames){

  # load file (column names defined elsewhere)
  temp <- read.delim(file = file, sep = '\t', header = F, col.names = columns) 

  # do some cleaning, merging, etc...

  # add to database
  dbWriteTable(conn, "Data", temp, append = TRUE)
}

Поскольку проблема выглядит одинаково как в R, так и в Python, я предполагаю, что есть какая-то проблема с тем, как я создал базу данных SQLite, или с тем, что она хранится на внешнем диске.

Любые идеи о том, что может пойти не так, или что я мог бы попробовать?

РЕДАКТИРОВАНИЕ:

Спасибо за все мысли!

Что касается индексации, как упоминалось @Waldi и @r2evans, это действительно помогает - я не имел в виду, что это не так, или что я не буду использовать индекс. Для самого сложного запроса, который я бы сделал (который по-прежнему фильтруется только по 3 переменным), полное индексирование сокращает время с ~ 6 минут до ~ 1,5 минут. Таким образом, очевидно, что индексы стоит использовать, но они сами по себе, похоже, не объясняют медлительность.

Что касается меньшей версии базы данных (@DavidP), хорошая идея - я только что попробовал несколько вещей и обнаружил, что в этом случае фактический размер базы данных не имеет большого значения для скорости, а скорее размер данных. возвращается из запроса (в частности, количество строк). Но даже в небольших случаях это кажется медленным — например, возврат всего 900 строк со всеми полностью проиндексированными занимает 1,3 секунды. Что, насколько я могу судить, все еще довольно медленно для простого SQL-запроса.

Что касается скорости HD (@Waldi и @dnoeth) - я не хотел верить, что это просто аппаратная проблема, но это определенно возможно. Я посмотрю, смогу ли я найти где-нибудь SSD, чтобы проверить это, и отчитаюсь.

Дополнительная информация для @Parfait (и других): '''

sqlite> .schema Data
CREATE TABLE `Data` (
  `Item` INTEGER,
  `Amplitude` REAL,
  `Electrode` INTEGER,
  `Time` INTEGER,
  `Subject` TEXT,
  `Hstatus` TEXT,
  `Word` TEXT,
  `Zipf_freq` REAL,
  `OLD` REAL,
  `Concreteness` REAL,
  `Visual_complexity` REAL,
  `Letter_number` REAL,
  `Morpheme_number` REAL
);
CREATE INDEX time_idx ON Data (Time);
CREATE INDEX idx ON Data (Hstatus, Electrode, Time);

'''

Что касается отказа от использования SELECT * - я пытался выбрать только одну переменную, а не все, и это не улучшило скорость. (а в этом приложении я уже вырезал ненужную мне информацию, поэтому кроме индексных переменных нужно выделить все).