SELECT запрос: по X записей из каждой категории

[papa]

Я где-то находил образец выполнения такой задачи. Но хоть убейте - не соображу\нахожу щас. Просьба помочь.
Задача: есть таблицы

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

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

CREATE TABLE `KATEGOPuu`(
                        `id_KATEGOPuu` INTEGER PRIMARY KEY,
                        `HAZBAHuE` TEXT,
                        `HAZBAHuE_nOLHOE` TEXT ASC,
                        `PODuTELb` INTEGER,
                        `PAZDEL` INTEGER,
                        `yPOBEHb` INTEGER,
                        `KOLu4ECTBO` INTEGER,
                        `PEDAKTuPOBAHuE` TEXT
                        );

Как мне получить "список категорий и по 1 записи в каждой из них"? Мои попытки, связанные с LIMIT не увенчались успехом:

SELECT k.`id_KATEGOPuu`, k.`HAZBAHuE`, k.`HAZBAHuE_nOLHOE`, k.`PODuTELb`, k.`yPOBEHb`, `KOLu4ECTBO`, k.`PEDAKTuPOBAHuE`, z.`HAZBAHuE` AS ZAnuCb 
FROM `KATEGOPuu` k, `ZAnuCu_OnuCAHuE` z, `ZAnuCu_KATEGOPuu` z_k 
WHERE z_k.`KATEGOPuia`=k.`id_KATEGOPuu` 
LIMIT 0,10;

Выдаёт 1 категорию и 10 записей в ней(10 раз категорию, и в каждой по 1 записи, разной). А мне надо: список категорий + колонка "ZAnuCb" в которой по 1 записи из каждой категории. Причём с сортировкой по алфавиту. Запрос вида

SELECT k.`id_KATEGOPuu`, k.`HAZBAHuE`, k.`HAZBAHuE_nOLHOE`, k.`PODuTELb`, k.`yPOBEHb`, `KOLu4ECTBO`, k.`PEDAKTuPOBAHuE`, z.`HAZBAHuE` AS ZAnuCb 
FROM `KATEGOPuu` k, `ZAnuCu_OnuCAHuE` z, `ZAnuCu_KATEGOPuu` z_k 
WHERE z_k.`KATEGOPuia`=k.`id_KATEGOPuu`
GROUP BY k.`id_KATEGOPuu` ORDER BY k.`yPOBEHb` ASC, k.`HAZBAHuE` ASC, z.`HAZBAHuE` ASC 
LIMIT 0,10;

Уходит в астрал. Если убираю "GROUP BY" - всё отрабатывает, но не правильно. Собственно: что я упустил? Как запрос составить?

 

[zabolots]

Это все будет в php-скрипте работать?
Не проще вам сначала получить список категорий, а потом в скрипте для каждой категории одну (LIMIT 1) запись для каждой?
Чтобы в астрал не уходил запрос, делайте индексы по полям сортировки и объединения.
<-------------- добавлено через 2045 сек. -------------->

В принципе, как я понимаю, он работает. Но очень долго обрабатывает. Индексы есть. Щас провожу эксперименты по ускорению. Может кто-что посоветует?

Индексы по полям id_KATEGOPuu, KATEGOPuia, id_ZAnuCu есть?
WHERE 0<=k.`id_KATEGOPuu` <- типо все категории - это зачем? если нужны все, то не нужно никаких условий (where ограничивает выборку, а не расширяет ее)... а у вас что, отрицательные категории?

Вопрос: сколько у вас записей в каждой из таблиц? Такой запрос (с индексами правильными) не должен выполняться заметное время при нескольких тысячах записей. Тормоза могут быть начиная с миллиона.

 

[sound_smith]

Записей много - неудивительно что тормозит. Можно посмотреть в сторону тюнинга конфига БД.

 

[papa]

Всё это в SQLite 3. Прога на C#. Щас смотрю запрос

SELECT k.`id_KATEGOPuu`, k.`HAZBAHuE`, k.`HAZBAHuE_nOLHOE`, k.`PODuTELb`, k.`yPOBEHb`, `KOLu4ECTBO`, k.`PEDAKTuPOBAHuE`,
(SELECT t0.`HAZBAHuE` FROM `ZAnuCu_OnuCAHuE` t0 WHERE k.`id_KATEGOPuu` =  t_k.`KATEGOPuia` AND t_k.`id_ZAnuCu` = t0.`id_ZAnuCu` ORDER BY t0.`HAZBAHuE` ASC LIMIT 0,1) AS ZAnuCb
FROM `KATEGOPuu` k, `ZAnuCu_KATEGOPuu` t_k
WHERE 0<=k.`id_KATEGOPuu`            <- типо все категории
GROUP BY k.`id_KATEGOPuu`
ORDER BY k.`yPOBEHb` ASC, k.`HAZBAHuE` ASC;

В принципе, как я понимаю, он работает. Но очень долго обрабатывает. Индексы есть. Щас провожу эксперименты по ускорению. Может кто-что посоветует?
<-------------- добавлено через 1824 сек. -------------->

Вопрос: сколько у вас записей в каждой из таблиц?

Таблица:
KATEGOPuu - ~1300 записей.
ZAnuCu_OnuCAHuE ~ 800 000 записей
ZAnuCu_KATEGOPuu ~ 1 000 000 записей

В "ZAnuCu_OnuCAHuE" - планируется около 20 000 000 записей.

Индексы по полям id_KATEGOPuu, KATEGOPuia, id_ZAnuCu есть?

Есть.
<-------------- добавлено через 2122 сек. -------------->

Записей много - неудивительно что тормозит. Можно посмотреть в сторону тюнинга конфига БД.

Например? Я рылся в инете и отключил всё, что может притормаживать. Т.е. база должна работать только на выдачу инфы. Может предложите конфиг?

 

[Black Hat]

В принципе, как я понимаю, он работает.

А мне кажется, что не понимаете. Профиль выполнения смотрели? С этого надо начинать, а не ставить куда попало индексы (вы хоть понимаете как индексы работают?) и менять как попало конфиг. Это мартышкин труд.
Используйте "EXPLAIN" и "EXPLAIN QUERY PLAN". Да, придется поизучать и поднапрячь мозг. Если хотите, чтобы я попробовал это сделать вместе с вами, выложите полную схему БД и дамп данных, на 10 тестовых сточках в таблице оптимизировать невозможно.

 

[Rammstein_91]

Например? Я рылся в инете и отключил всё, что может притормаживать.

А прога (вопрос в большей степени про расположение бд) будет доступна онлайн или офлайн? Если онлайн, то может скорости и пропускной способности сервера не хватает?

 

[papa]

База данных на 2 гига(sqlite файл - дамп базы данных сайта), на ~800 000 записей, и будет только расти.
База для проги - выкачивается из инета. И храниться в папке с прогой.
Была изменена задача. Т.к. запрос более 30 секунд, а таблица(про информацию: какая запись - в какой категории(т.е. 1 запись может принадлежать нескольким категориям)), при 800 000 уникальных записей, составляет 20 000 000, было решено изменить задачу. Если в кратце: главная задача: показать больше количество записей. На моменте создание базы SQLite была приделана сортировка, т.е. записи сортируются на входе(запрос к скрипту, он создаёт файл базы для проги, и даёт на неё ссылку, через которую она перетекает на комп), и при считывании данных(локально) вычёркиваем "ORDER BY", т.к. это самая трудоёмкая задача при запросе. А в самой проге пишем(типо работает цикл на вывод всех записей) "Если количество записей(записанных в переменную на вывод в контрол) кратно 10000(например) - Отрисовываем записи в контроле(скажем нечто с реализованным "NotifyCollectionChangedEvent")". В результате: "перерисовка каждые секунд 15" и постепенно выводятся записи. Человек радуется.