SQLite SELECT рядом стоящие записи

[papa]

Вообщем. База SQLite 3. Есть такое:

Спойлер: Таблицы

CREATE TABLE `ZAnuCu_OnuCAHuE`(
`id` INTEGER PRIMARY KEY,
`HAZBAHuE` TEXT ASC,
`MODELb` TEXT,
`KPATHOCTb` INTEGER,
`DOCTynHIOCTb` INTEGER,
`KAPTuHKA` TEXT,
`KATEGOPuia` INTEGER,
`PAZDEL` INTEGER,
`CTATyC` TEXT,
`PEDAKTuPOBAHuE` TEXT);

CREATE TABLE `ZAnuCu_KATEGOPuu`(
`id` INTEGER ASC,
`KATEGOPuia` INTEGER ASC,
`PAZDEL` INTEGER ASC,
`PEDAKTuPOBAHuE` TEXT);

CREATE UNIQUE INDEX IF NOT EXISTS `uHDEKC_id` ON `TOBAPbl_OnuCAHuE` (`id`);
CREATE INDEX IF NOT EXISTS `uHDEKC_HAZBAHuE` ON `TOBAPbl_OnuCAHuE` (`HAZBAHuE`);

Я пытаюсь получить записи, стоящие рядом с Х.
Скажем: Есть элемент, куда я вывел список. Прокрутил я его, и тут сработал скрипт. Я даю команду "Дай мне 500 записей до и после Х-ой записи". Так вот. Как сделать это на SQLite?
Единственное, где я вообще нашёл упоминание об "Select рядом стоящие записи" это Для просмотра ссылки Войди или Зарегистрируйся - "Почитай у Кена Хендорсона про оператор Select и рядом стоящие записи.". Вот только я что-то не понял: где это? Может видел это место, но не сообразил "то, что нужно". Прошу помочь разобраться с вопросом.

Дополнение: цель - сделать "ленивую подргузку, когда ползунок дошёл до места". Т.е. прокрутили до 10%(от конца) - скрипт сработал - список обновился. И сделать это быстро. А значит: повесить на SQLite эту задачу. Собствено я сюда и обратился.


В базе записи по алфавиту, как и в выводе. Т.е. когда прокрутил - Я знаю из какой категории, и какой по счёту записи(по алфавиту отсортировано в базе "`HAZBAHuE` TEXT ASC," , и в выводе). Также я знаю какие категории идут до и после, и количество записей в каждой. Проблема в реализации запроса.

 

[Black Hat]

рядом стоящие записи.

В базе записи по алфавиту

Сами по себе эти словосочетания - ересь. Мыслите не реляционно. Рано или поздно поймете.

Показали бы выглядит запрос сейчас.

А вообще, у вас есть "HAZBAHuE" последней записи, до которой прокручено. Соответственно, нужно отсортировать вверх (до) или вниз (после) и поставить LIMIT 500.

Что-то типа

WHERE HAZBAHuE < "Ваше последнее название"
ORDER BY HAZBAHuE DESC
LIMIT 500

PS. Нашел орфографическую ошибку - "DOCTynHIOCTb" исправьте на "DOCTynHOCTb"

 

[Black Hat]

Тестовые данные:

INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (1, 'Товар A', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');
INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (2, 'Товар В', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');
INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (3, 'Товар Б', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');
INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (4, 'Товар Д', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');
INSERT INTO `ZAnuCu_OnuCAHuE` VALUES (5, 'Товар Г', 'Модель', 1, 1, 'img/blank.png', 1, 1, 1, '');

>> В базе записи по алфавиту.

select * from ZAnuCu_OnuCAHuE;

1|Товар A|Модель|1|1|img/blank.png|1|1|1|
2|Товар В|Модель|1|1|img/blank.png|1|1|1|
3|Товар Б|Модель|1|1|img/blank.png|1|1|1|
4|Товар Д|Модель|1|1|img/blank.png|1|1|1|
5|Товар Г|Модель|1|1|img/blank.png|1|1|1|

А вот и не по алфавиту. Просто у вас по порядку вставлялось, поэтому так выглядит.

Так можно получить "до":

SELECT *
FROM ZAnuCu_OnuCAHuE
WHERE HAZBAHuE < 'Товар Б'
ORDER BY HAZBAHuE DESC
LIMIT 500;

1|Товар A|Модель|1|1|img/blank.png|1|1|1|

Так можно получить "после":

SELECT *
FROM ZAnuCu_OnuCAHuE
WHERE HAZBAHuE > 'Товар Б'
ORDER BY HAZBAHuE ASC
LIMIT 500;

2|Товар В|Модель|1|1|img/blank.png|1|1|1|
5|Товар Г|Модель|1|1|img/blank.png|1|1|1|
4|Товар Д|Модель|1|1|img/blank.png|1|1|1|

 

[papa]

PS. Нашел орфографическую ошибку - "DOCTynHIOCTb" исправьте на "DOCTynHOCTb"

Поправил! Благодарю.

С "ORDER BY HAZBAHuE" выводит записи, в тестовой базе, за 7.5 секунд. Без - 50 милисекунд.
Тестовый запрос:

Спойлер: Запрос

SELECT t.`id`,
t.`HAZBAHuE`,
t.`MODELb`,
t.`KPATHOCTb`,
t.`DOCTynHIOCTb`,
t.`KAPTuHKA`,
t_k.`KATEGOPuia`,
t.`KOLOHKA1239`,
t.`KOLOHKA1267`,
t.`KOLOHKA1272`
FROM
`ZAnuCu_OnuCAHuE` t,
`ZAnuCu_KATEGOPuu` t_k
WHERE t_k.`KATEGOPuia` IN (64, 2680, 29361, 2720, 2656, 2800, 2727, 2612, 2681, 2768, 2729, 29051, 2882, 2750, 2761, 29208, 29209, 2608, 29421, 2791, 2745, 2676, 29419, 2790, 28470, 28472, 28586, 2715, 2749, 24560, 29362, 2596, 29210, 30893, 2734, 2884, 29813, 29946, 2717, 28542, 29427, 29227, 28585, 30228, 28482, 28483, 29416, 29429, 2685, 2880, 2786, 22895, 29435, 2592, 2595, 2590, 2643, 2642, 28547, 22597, 2846, 29844, 2740, 2865, 2598, 2627, 28541, 29428, 29811, 29975, 2647, 2633, 2854, 29955, 29804, 29954, 24988, 2638, 2736, 2883, 2667, 2626, 29727, 2764, 2765, 2655, 28469, 29802, 29962, 29792, 29786, 2738, 22684, 28587, 24722, 2618, 29797, 2847, 2849, 2861, 29231, 29053, 24723, 2624, 22598, 29783, 29417, 29412, 2855, 2856, 2848, 29789, 30046, 28588, 29221, 29234, 30060, 30053, 29800, 29963, 29899, 2763, 2670, 29822, 2683, 2639, 2869, 29935, 29940, 2798, 29782, 29938, 2762, 29226, 2615, 2863, 30240, 30236, 2772, 2775, 2857, 28589, 28600, 28578, 29617, 28551, 2603, 2682, 30048, 30066, 28686, 29212, 2724, 29434, 2755, 29614, 2650, 2671, 2640, 2793, 29729, 2663, 29418, 29422, 28471, 29423, 29430, 2780, 2637, 29411, 2607, 24724, 29232, 2789, 2748, 29027, 2784, 30059, 29414, 28549, 2859, 2787, 2723, 29436, 29831, 29843, 2614, 30040, 2664, 2674, 2728, 2636, 24896, 29441, 29206, 23929, 29941, 2747, 29942, 2737, 2741, 2795, 2732, 28546, 28594, 28596, 30237, 29363, 2823, 2845, 29842, 2646, 2872, 2735, 29214, 2635, 29799, 2866, 29943, 29220, 29219, 30230, 2619, 2773, 28603, 28581, 2653, 2842, 29618, 2873, 2742, 29971, 29834, 29790, 29785, 2691, 28590, 2599, 28604, 28582, 28591, 2644, 2629, 29728, 2666, 2679, 2771, 2660, 30231, 28605, 28583, 2890, 2875, 2889, 24821, 30234, 30894, 29364, 29956, 2593, 2853, 2611, 2776, 2613, 29845, 22894, 2886, 2602, 2725, 29437, 2692, 2634, 2662, 2684, 2652, 29987, 2631, 30051, 30058, 2844, 28544, 29425, 2767, 29285, 2601, 22356, 29431, 2862, 2739, 29616, 30238, 2721, 29432, 2783, 2657, 29819, 29947, 29833, 29968, 2621, 30047, 30065, 2668, 29791, 29224, 2616, 28592, 28606, 28477, 28613, 28609, 28612, 28607, 28593, 28552, 28597, 28610, 28608, 29223, 2610, 2716, 29803, 29964, 30049, 30067, 2782, 29233, 2858, 29217, 2673, 30056, 2594, 29812, 29945, 2609, 2591, 29983, 2870, 30020, 2677, 30039, 29438, 29444, 2759, 30041, 30043, 30042, 2757, 2669, 29218, 29829, 29230, 28598, 28595, 29730, 2718, 2726, 28478, 30235, 29936, 29982, 2605, 2753, 2756, 2876, 24958, 2731, 2722, 29433, 2887, 29827, 29835, 29967, 29973, 2877, 29200, 23653, 2769, 29228, 29814, 29211, 29820, 2885, 29959, 29960, 29961, 30052, 29958, 2751, 29225, 2754, 28545, 29424, 29415, 29426, 2630, 2730, 29229, 29808, 29976, 29830, 29828, 30227, 2622, 29832, 29794, 29951, 30050, 29796, 29952, 29795, 2686, 24626, 2688, 2687, 29818, 29948, 29120, 29816, 29949, 29957, 29787, 30768, 29944, 29207, 24718, 2641, 2785, 29825, 29824, 29826, 2852, 2746, 2719, 28601, 28579, 29823, 29966, 24717, 30064, 29798, 29953, 2743, 29442, 29205, 28479, 2878, 2675, 2774, 29937, 29984, 29986, 28611, 28599, 28577, 2625, 2794, 30057, 2871, 29815, 2632, 2744, 24719, 29817, 29950, 2779, 2604, 2651, 28397, 2648, 29810, 29977, 29939, 2623, 2867, 2597, 2868, 2620, 24561, 29215, 2600, 29439, 2690, 29801, 29965, 30045, 28480, 30061, 30063, 29788, 29793, 29805, 29974, 29809, 29978, 29807, 29979, 2879, 29806, 29980, 2881, 29934, 29981, 2888, 30239, 22682, 30054, 2777, 2778, 30766, 30068, 22683, 2645, 28602, 28580, 2628, 2665, 2678, 2770, 2659, 30229, 2766, 29972, 2733, 28584, 28474, 28481, 28475, 29202, 24017, 29784, 30767, 2654, 29846, 2851, 2781, 29052, 2617, 29284, 28463, 28550, 2658, 30038, 30226, 2661, 24627, 29420, 2792, 2843, 30895, 30055, 2841, 2864, 2860, 30233, 2796, 2797, 22782, 29222, 2850, 30044, 2672, 29213, 29216, 2760, 29443, 29204, 29440, 29203, 29413, 29821, 29985, 29970, 30062, 29836, 29969, 30232, 30037, 29615) AND t_k.`id` = t.`id` ORDER BY t.`HAZBAHuE` ASC LIMIT 0, 1000

Базу уже привёл в 1 посте. Как заставить работать быстрее? При создании базы происходит: " GROUP BY `id` ORDER BY v.`name` ASC" - это записывает в базу SQLite, в которой я копаюсь(Таблицы в 1 посте), и из неё пытаюсь выводить уже у себя локально данные. Т.е. пытаюсь ускорить максимально. Какие-нибудь предложения есть?

 

[Black Hat]

Специфику ускорения запросов под SQLite не знаю.
Я бы посмотрел как профилировать запросы.
Как работать с планами запросов "EXPLAIN QUERY PLAN ваш-SQL-запрос" и "EXPLAIN ваш-SQL-запрос".

Еще вопрос - почему SQLite? Это "урезанная" РСУБД. Под MySQL я бы подсказал.

 

[papa]

Еще вопрос - почему SQLite?

Идея: сайт. Его база данных(в данном случае MariaDB, но это неважно) перегоняется в SQLite файл(php скрипт). Он то и должен открываться везде, где можно. Собственно: SQLite - переносная. В прогу(C#) встраивается dll-а для работы с ней(стандартная). Сначала, раньше было CSV файл. Но потом возникли проблемы(объёмы данных) -> SQLite показалась подходящей(я не спец. Рассмотрел - показалось - перекрестился - сделал).

Тогда такой вопрос: у меня есть список категорий, из которых я хочу получить записи(Порядок их важен. По алфавиту, а не по id). Для примера:

Спойлер: Тестовый запрос

SELECT t.`id`,
t.`HAZBAHuE`,
t.`MODELb`,
t.`KPATHOCTb`,
t.`DOCTynHIOCTb`,
t.`KAPTuHKA`,
t_k.`KATEGOPuia`,
t.`KOLOHKA1239`,
t.`KOLOHKA1267`,
t.`KOLOHKA1272`
FROM
`ZAnuCu_OnuCAHuE` t,
`ZAnuCu_KATEGOPuu` t_k
WHERE t_k.`KATEGOPuia` IN (64, 2680 ... 'и так 579 категорий') AND t_k.`id` = t.`id` ORDER BY t.`HAZBAHuE` ASC LIMIT 0, 1000

. Так вот. Я добился: чтобы товары шли без сортировки в алфавитном порядке(сразу в базе. Добавил в "ZAnuCu_KATEGOPuu" поле "Название товара" и его отсортировал на входе. Мне нужен алфавитный порядок в категориях, а не в таблице товаров. Т.е. у меня есть список категорий(отсортированный, не по id) и есть отсортированный список товаров(по названию)).


А как мне сделать так, чтобы было: из категории "64 дай мне всё что есть, если меньше 1000 записей", "2680 дай мне всё что есть, если меньше 1000 записей" и так далее, но чтобы при достижении сумарного количества записей "в 1000 едениц" оно мне выдавало результат(прилагая ему список категорий.)?

 

[zabolots]

А как мне сделать так, чтобы было: из категории "64 дай мне всё что есть, если меньше 1000 записей", "2680 дай мне всё что есть, если меньше 1000 записей" и так далее, но чтобы при достижении сумарного количества записей "в 1000 едениц" оно мне выдавало результат(прилагая ему список категорий.)?

Ну и названия полей у вас
То есть задача такая: получить список товаров только из тех категорий, в которых этих товаров менее 1000 штук, верно? Первым запросом определяете id этих категорий, вторым выбираете записи.
Примерно так: select * from opisanie where category_id IN (select category_id from opisanie group by category_id having count(*)<1000) - это на MySQL так, наверное и в sqlite так же.

 

[papa]

В общем вопрос решился таким запросом: индексация по колонкам, которым надо проводить сортировку; Колонки таблицы в количестве 10-20 штук; Не использовать программу "DB Browser for SQLite", т.к. в ней запрос выполняется больше по времени раз в 10.(при сортировке); и поиграться с настройками SQLite при соединении(соединились с базой, отправили настройки - после запрос.)

В общем тестовый запорс в проге выполняется не более 0.5 секунды. А в "DB Browser for SQLite" - 6-7 секунд

 

[Rammstein_91]

Собственно: SQLite - переносная.

Присоединяясь к Black Hat, хотел бы всё таки уточнить - а почему базу не повесить в облаке на мощном сервере? Или задача подразумевает офлайн подключение с компов, не имеющих доступ в инет? Хотя сейчас это редкость, а в случае если доступ в инет закрыт (только локалка), то в файрволе/сервере просто открывается определенный порт для доступа к базе.

 

[papa]

Человек может сидеть на даче, без инета, на батарейке. Подразумевается самый неприятный случай "без инета".
<-------------- добавлено через 289 сек. -------------->
Человек может сидеть на даче, без инета, на батарейке. Подразумевается самый неприятный случай "без инета".


По поводу вопроса: актуальность пропала, т.к. были изменены некоторые обстоятельства задачи.

Тем не менее, по поводу "Дай мне 500 записей до и после Х-ой записи" - делается(после долгих раздумий, пришёл к выводу) 2 запросами.
Что-то вроде "SELECT name FROM table WHERE name<='ssss' ORDER BY name LIMIT 0,500" и "SELECT name FROM table WHERE name<='ssss' ORDER BY name DESC LIMIT 0,500". От "ORDER BY", как от самой друдоёмкой задачи можно избавится(при чтении данных) лишь сортировкой в самой базе. Т.е. в саму базу записывать записи в определённом порядке.