Sqlite3, связь один ко многим, как ускорить выборку по ключу или правильно написать select. join

@enanthamelle · Регистрация: 14.02.2015

Студворк — интернет-сервис помощи студентам

вот так создается таблица:

C++
1
sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS lib (id INT PRIMARY KEY NOT NULL, lid INT, name CHAR[40], path CHAR[256], info TEXT, sid INT);", NULL, 0, &zerrmsg);

вот селекты: запрос по полю id:

C++
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
t2 = get_time(0);
       sqlite3_exec(db,"BEGIN;",0,0,&err);
       ........................
        while (numlist != NULL)
        {
            sqlite3_prepare16(db,(WCHAR*) L"SELECT id, lid, name, path, info, sid FROM lib WHERE ID = ?;", -1, &stmt, 0);
            sqlite3_bind_int(stmt,1,numlist->number);
            while (sqlite3_step(stmt) == SQLITE_ROW)
            {
                ...................
            }
            sqlite3_finalize(stmt);
            numlist = numlist->next;
        }
        sqlite3_exec(db,"COMMIT;",0,0,&err);
        t2 = get_time(t2);
        cout << " after scroll: " << t2 << endl;

запрос по lid(library id):

C++
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sqlite3_prepare16(db,(WCHAR*) L"SELECT id, lid, name, path, info, sid FROM lib WHERE lid = ?;", -1, &stmt, 0);
            sqlite3_bind_int(stmt,1,lid);
            double t3 = get_time(0);
            while (sqlite3_step(stmt) == SQLITE_ROW)
            {
                t1 = get_time(0);
                ................................
                t1 = get_time(t1);
                cout << " one row: " << t1 << endl;
                t2 = get_time(0);
            }
            t2 = get_time(t2);
            cout << " after scroll: " << t2 << endl;
            t3 = get_time(t3);
            cout << "scroll rows: " << t3 << endl;
        sqlite3_finalize(stmt);

функция get time:

C++
1
2
3
4
5
double get_time (double t1)
{
    if (t1 == 0) return clock();
    else return (clock() - t1);
}

первый запрос выдает t2 = 60 на ~1000 записей из 1000000, тобишь 6 милисекунд
второй - t1 = 0-1, t2 = 1500 - 3000 (1,5 - 3 секунды), t3 - от t2 отличается на пару милисекунд

итак: по смыслу программы один фаил (id) принадлежит только к одной категории (lid) и необходимо будет много раз запрашивать список из файлов одной категории, и крайне необходимо чтобы список этот формировался максимально быстро (ну никак не за 1,5 секунды)
внимание вопросы:
1) собсно до меня только сейчас дошло наконец что время второго запроса - это просмотр всей базы, ибо lid не уникальный ключ, это так? и есть ли способ это обойти?
2) если создать таблицу категорий скажем: CREATE TABLE category (lid PRIMARY KEY NOT NULL, name GHAR[50]), а в таблице lib установить foreign key, то есть: CREATE TABLE lib (id INT PRIMARY KEY NOT NULL, lid INT, name CHAR[40], path CHAR[256], info TEXT, sid INT, FOREIGN KEY (lid) REFERENCES category(lid)), то как правильно написать select результат которого будет подобен результату моего 2-го select-а?
я конечно знаю про join, и пыталась найти такую информацию, но не смогла почерпнуть оттуда решение потому что ~~я блондинка~~ либо авторы пускаются в совсем непонятные простаранные объяснения, либо приводятся примеры где объеденяются две таблицы и т.п.
3) если произвести действия указанные во втором вопросе, насколько это ускорит выборку?

Добавлено через 4 часа 19 минут
да, и еще вопрос, наверно самый интересный, но увы врядли имеющий практическую ценность для данной задачи:

насколько я понимаю, при первом вызове sqlite3_step должна сформироваться таблица результатов и по ней уже он переходит на следующую строку при каждом вызове, каждую строчку он обрабатывает меньше чем за микросекунду, однако: между моментами когда цикл последний раз отработал и когда программа вышла из цикла проходит аж от 1,5 секунд.. насколько я понимаю после вызова t2 = get_time(0) у нас происходит следущее: вызывается sqlite3_step, она должна перескочить на следующую строку, следующей строки нет и она выдает НЕ SQLITE_ROW, while завершается.. так что в этом процессе может занимать так много времени?

@enanthamelle · 21.03.2016, 08:46 **[ТС]**

Решение нашла сама, может кому пригодится: оказалось foreign key никак на скорость запроса не влияет, я не туда копала)
надо было после создания таблицы - создать индекс на поле lid:

C++
1
sqlite3_exec(db, "CREATE INDEX ilib ON lib(lid);", NULL, 0, &zerrmsg);

и тогда - аллилуйя: 665 записей разбросанных по таблице и все тот же селект: 8 микросекунд
и вот что странно - записала в еще 500 записей тот же lid и селект отработал за 2 микросекунды.. вот это как?
но в целом порог в максимальные 2 милисекунды преодолен)

Новые блоги и статьи Все статьи Все блоги /
Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Programma_Boinc 28.12.2025 Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Налог на собак: https:/ / ********/ gallery/ V06K53e Финансовый отчет в Excel: https:/ / ********/ gallery/ bKBkQFf Пост отсюда. . .	Кто-нибудь знает, где можно бесплатно получить настольный компьютер или ноутбук? США. Programma_Boinc 26.12.2025 Нашел на реддите интересную статью под названием Anyone know where to get a free Desktop or Laptop? Ниже её машинный перевод. После долгих разбирательств я наконец-то вернула себе. . .	Thinkpad X220 Tablet — это лучший бюджетный ноутбук для учёбы, точка. Programma_Boinc 23.12.2025 Рецензия / Мнение/ Перевод Нашел на реддите интересную статью под названием The Thinkpad X220 Tablet is the best budget school laptop period . Ниже её машинный перевод. Thinkpad X220 Tablet —. . .	PhpStorm 2025.3: WSL Terminal всегда стартует в ~ and_y87 14.12.2025 PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .	Как объединить две одинаковые БД Access с разными данными VikBal 11.12.2025 Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.
Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .	Поиск "дружественных имён" СОМ портов Argus19 22.11.2025 Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .	Сколько Государство потратило денег на меня, обеспечивая инсулином. Programma_Boinc 20.11.2025 Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .