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 милисекунды преодолен)

Новые блоги и статьи Все статьи Все блоги /
Символьное дифференцирование igorrr37 13.02.2026 / * Программа принимает математическое выражение в виде строки и выдаёт его производную в виде строки и вычисляет значение производной при заданном х Логарифм записывается как: (x-2)log(x^2+2) -. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .	И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»
SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL3_image 8Observer8 10.02.2026 Содержание блога Библиотека SDL3_image содержит инструменты для расширенной работы с изображениями. Пошагово создадим проект для загрузки изображения формата PNG с альфа-каналом (с прозрачным. . .	Установка Qt-версии Lazarus IDE в Debian Trixie Xfce volvo 10.02.2026 В общем, достали меня глюки IDE Лазаруса, собранной с использованием набора виджетов Gtk2 (конкретно: если набирать текст в редакторе и вызвать подсказку через Ctrl+Space, то после закрытия окошка. . .