Как составить оптимальный запрос в sqlite

Никогда не используй SELECT в WHERE. Вынеси его в JOIN.

Что-то вроде

SELECT id_parent FROM table1 AS t 
INNER JOIN SELECT MAX(date) date, id_parent FROM table1 t_date ON t_date.id_parent = id_parent
WHERE id_status = 2

Писал наощупь, синтаксис мог и налажать. Ну и за оптимальность тоже не скажу, но это точно лучше, чем подзапрос в WHERE.

нет, не правильный. вообще это похоже на задачу с собеседования, так что гуляй.

Что-то сомнительное утверждение.

JOIN же это гарантированный MATERIALZE, который не всегда полезен.

EXPLAIN QUERY PLAN вполне приличный с вложенным подзапросом, на мой взгляд. Точно проще, чем с JOIN, CTE, ROW_NUMBER() и прочими вариантами.

Индексы на date ещё навесить и совсем хорошо. Вроде.

id|parent|notused|detail                                   |
--+------+-------+-----------------------------------------+
 2|     0|      0|SCAN t                                   |
 8|     0|      0|CORRELATED SCALAR SUBQUERY 1             |
14|     8|      0|SEARCH table1 USING INDEX table1_date_IDX|

vs

id|parent|notused|detail                                       |
--+------+-------+---------------------------------------------+
 3|     0|      0|MATERIALIZE tx                               |
10|     3|      0|SCAN tm                                      |
12|     3|      0|USE TEMP B-TREE FOR GROUP BY                 |
54|     0|      0|SCAN tx                                      |
56|     0|      0|SEARCH t USING INDEX table1_date_IDX (date=?)|

Возможно я что-то не знаю про SQLite. Но по опыту запросов T-SQL, запрос в WHERE это всегда было плохо.

В общем случае, не глядя на данные и окружение - спорить не готов. Автору проще самому нагенерировать предполагаемых данных, да проверить все варианты.

В PG я как-то экспериментировал похожий вопрос - к моему удивлению вот именно с подзапросом получалось быстрее всего.

Можно сделать с использованием оконных функций:

select
  id_parent
from
(
  select
    id_parent,
    first_value(id_status) over (partition by id_parent order by date desc) st
  from
    t1
)
where
  st = 2
group by
  id_parent

Отрабатывает с тем же выводом, что и мой вариант. По скорости пока не понятно. Как посоветовали выше, заполню данными таблицу, чтобы сравнить производительность запроса.

Почему select в where плох? Читал пару статей по плохим практикам при сырых запросах в sqlite, не видел ничего подобного.

А я бы зафигачил какой-нить подходящий индекс (лень думать, но на вскидку – (parent_id, date)), сделал бы select * order by parent_id, date – и сворачивал бы вручную, как в map-reduce.

А поскольку мы имеем дело с embedded-движком, хрен вы меня по скорости переплюнете со своими джойнами да подзапросами.

Вот так ещё можно: https://dbfiddle.uk/ebKq93pY

select * from (
  select *, max(date)
  from tbl
  group by id_parent
)
where id_status = 2

SQLite при max гарантирует что значения в других колонках будут из той же строчки что и сам max: https://www.sqlite.org/lang_select.html#bareagg

Special processing occurs when the aggregate function is either min() or max().

десятки тысяч

это в современном соевом мире, где паинт больше сотни мегабайт весит, а у джавафриков в репозиториях вся база хранится

select distinct(id_parent) from такая таблица

Я из описанного тобой такой бы запрос составил (ну влоб)

select id_parent from (select id_parent, id_status, max(date) from tbl group by id_parent) where id_status = 2;

Короче раз это первоеч то в голову пришло, то оно скорее всего неправильно…

Я еще посмотрел выше тож самое считай ряяя - значит точно неправильно

в таких случаях надо смотреть или наличие индексов с последующим профилированием запроса. Или добавлять(если есть возможность) нужные индексы. И вообще то говоря «десятки тысяч записей» - это просто ни о чем. Даже если нету индексов(конечно если у тебя условное embedded на 32Кб оперативки - то тогда имеет значение).

Под бессонницу поиграл в эту игру на 1 миллионе записей.

Похоже ваш вариант для SQLite бесспорно лучший при случае «нет вообще никаких индексов».

Подзапрос без индексов - просто катастрофа. Вот так точно нельзя делать.

Как только делаешь индекс по id_parent - все варианты практически одинаковые по времени (в т.ч. и с подзапросом). Ваш вариант становится чуть хуже самого себя, но остается лучшим всех остальных.