ReSQL.ru
     
powered by simpleCommunicator - 2.0.37     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Оптимальное создание таблиц для выборки
12 сообщений из 12, страница 1 из 1
Оптимальное создание таблиц для выборки
    #39424301
blur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
blur
Гость
Добрый день.
...
Рейтинг: 0 / 0
21.03.2017, 17:40
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39424310
blur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
blur
Гость
Извините, случайно опубликовал)
Суть вопроса такая. Нужно мне создать оптимизированный для выборки данных архив.
Пока пробую такой вариант, создаю две таблицы:
t1 (ID INTEGER PRIMARY KEY AUTOINCREMENT, UnixDateTime INTEGER NOT NULL UNIQUE)
t2 (ID INTEGER PRIMARY KEY AUTOINCREMENT, UnixDateTime, Value1, Value2)
Отношение между t1 и t2 думаю сделать по столбцу UnixDateTime как один ко многим (в таблице t2 будет 60 строк на каждое значение UnixDateTime в t1).
Выборка будет в основном такая
select * from t1 where UNIXDateTime between ...
и к ней join из t2
Подскажите это нормальный вариант или есть решения как это оптимальнее организовать?

Буду благодарен за любые советы.
...
Рейтинг: 0 / 0
21.03.2017, 17:54
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39424313
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
Жуть какая.
t1 - выкинуть нафиг.
На t2, на поле UnixDateTime сделать неуникальный индекс.
Все.
...
Рейтинг: 0 / 0
21.03.2017, 17:59
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39424330
blur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
blur
Гость
White Owl,

Спасибо за совет. Это была моя первая мысль. Но забыл сказать про объем, в t2 будет писаться 60 строк каждые 10 сек (может и реже, смотря как настройку выставят) примерно 15кк строк в месяц, соответственно в t1 в 60 раз меньше. Не будет выигрыша в скорости если работать с двумя таблицами?
...
Рейтинг: 0 / 0
21.03.2017, 18:34
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39424332
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
blurWhite Owl,

Спасибо за совет. Это была моя первая мысль. Но забыл сказать про объем, в t2 будет писаться 60 строк каждые 10 сек (может и реже, смотря как настройку выставят) примерно 15кк строк в месяц, соответственно в t1 в 60 раз меньше. Не будет выигрыша в скорости если работать с двумя таблицами?Конечно нет.
Запрос на одну таблицу ВСЕГДА быстрее чем запрос на две таблицы.
...
Рейтинг: 0 / 0
21.03.2017, 18:42
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39425257
blur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
blur
Гость
White Owl,

Объясни немного про индекс, который создается руками. Это же тоже столбец как и rowid (который автоматически создается)?
Просто хотел такой запрос сделать: select myIndex from t1 в результате ошибка, что нету столбца, а select rowid from t1 выполняется.
Это же нормально если я проиндексировал столбец уже наполненной таблицы?
...
Рейтинг: 0 / 0
22.03.2017, 21:23
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39425265
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
blurWhite Owl,

Объясни немного про индекс, который создается руками. Это же тоже столбец как и rowid (который автоматически создается)?Нет. Нет.
Индекс это отдельный объект в базе данных. Ты его создаешь и больше никогда не трогаешь. Сервер сам сообразит и обновить индекс если нужно и использовать его если это удобно.
rowid это не автоматический столбец, а внутренний объект таблицы. Его можно рассматривать как столбец, но не всегда. И для хорошей жизни, лучше забудь о нем и никогда не используй. Это вещь очень технически-зависимая и для логики приложения часто вредная.

blurЭто же нормально если я проиндексировал столбец уже наполненной таблицы?Да, это нормально.



ты делаешь:
Код: sql
1.
2.
create table t2 (ID INTEGER PRIMARY KEY AUTOINCREMENT, UnixDateTime, Value1, Value2);
create index myIndex on t2(UnixDateTime);


А потом просто:
Код: sql
1.
select * from t2 where UnixDateTime between 1 and 1000

И если сервер решит что использование индекса в данном случае оправдано - он будет использован.
Узнать используется ли индекс и если да, то какой (если их несколько) и как именно (процент выборки, предсказания и реальность попадания) ты можешь используя команду EXPLAIN QUERY PLAN.
...
Рейтинг: 0 / 0
22.03.2017, 21:56
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39425316
blur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
blur
Гость
White OwlИндекс это отдельный объект в базе данных.
Т.е. я поэтому и не могу его значения селектом посмотреть?
Здесь (sqlite.org) я про это не увидел.

White Owlrowid это не автоматический столбец, а внутренний объект таблицы.
С этим то вроде как понятно, для сравнения написал (пока еще могу путаться в терминологии).

Так более или менее вкурил. Немного экспериментов с выборками и снова сюда.
...
Рейтинг: 0 / 0
23.03.2017, 00:32
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39425922
blur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
blur
Гость
Протестировал в SQLiteStudio разные вариации такого запроса на 1 млн. строк:
Код: sql
1.
2.
3.
4.
SELECT dBaseTableRooms.rNumber, rName, dBaseTableMeasurments.mID, strftime('%d.%m.%Y %H:%M:%S', mUnixDateTime, 'unixepoch'), sID1, sRTValue1, sID2, sRTValue2, sID3, sRTValue3, sID4, sRTValue4
FROM dBaseTableRooms
INNER JOIN dBaseTableMeasurments ON dBaseTableMeasurments.mRoomID = dBaseTableRooms.rID
WHERE mUnixDateTime BETWEEN 1490054400 AND 1490313540 AND mUnixDateTime % 1.0 == 0;


Пробовал разные варианты: с индексом по mUnixDateTime, без индекса.
Скорость выполнения обоих вариантов очень близка к 0.
Походу надо тестировать не на 1 млн. записей, а хотя бы на 10 млн. или еще лучше на 100 млн.

EXPLAIN QUERY PLAN показал что индекс (когда он есть используется) используется.
Только вот не понятно как сам план запроса, который студия показывает, читать.
А то там в столбце p2 большая разница получается.
...
Рейтинг: 0 / 0
23.03.2017, 16:06
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39425996
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
blurWhite OwlИндекс это отдельный объект в базе данных.
Т.е. я поэтому и не могу его значения селектом посмотреть?
Здесь (sqlite.org) я про это не увидел.Потому что это считается само-собой разумеющимся.
То что описано в теоретических учебниках по абстрактным базам данных, и является реальностью во всех реальных СУБД, не нуждается в описании конкретной команды :)
Или ты ожидаешь в инструкции по эксплуатации какого-нибудь Москвича найти описание принципов построения колеса?
...
Рейтинг: 0 / 0
23.03.2017, 18:03
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39426001
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
blurПротестировал в SQLiteStudio разные вариации такого запроса на 1 млн. строк:
Код: sql
1.
2.
3.
4.
SELECT dBaseTableRooms.rNumber, rName, dBaseTableMeasurments.mID, strftime('%d.%m.%Y %H:%M:%S', mUnixDateTime, 'unixepoch'), sID1, sRTValue1, sID2, sRTValue2, sID3, sRTValue3, sID4, sRTValue4
FROM dBaseTableRooms
INNER JOIN dBaseTableMeasurments ON dBaseTableMeasurments.mRoomID = dBaseTableRooms.rID
WHERE mUnixDateTime BETWEEN 1490054400 AND 1490313540 AND mUnixDateTime % 1.0 == 0;


Пробовал разные варианты: с индексом по mUnixDateTime, без индекса.
Скорость выполнения обоих вариантов очень близка к 0.
Походу надо тестировать не на 1 млн. записей, а хотя бы на 10 млн. или еще лучше на 100 млн.Там не только количество записей играет роль, но и ширина записей (количество колонок в таблице). И распределение значений индексируемого поля.
Кроме увеличения количества записей можешь для наглядности увеличить-уменьшить ожидаемую выборку в between. Если твоя выборка должна вернуть одну-две записи, то с индексом ты увидишь ускорение.
А если твоя система даже сейчас без индекса отдает результат очень быстро - то можешь замедлить всю систему. Перейди на более слабый компьютер, положи файл базы на сетевой диск... Добейся, в общем, чтобы твой запрос на не-индексированой таблице отрабатывал секунд за десять, а потом создай индекс.

blurEXPLAIN QUERY PLAN показал что индекс (когда он есть используется) используется.
Только вот не понятно как сам план запроса, который студия показывает, читать.
А то там в столбце p2 большая разница получается.А документацию читать не модно? https://www.sqlite.org/eqp.html
...
Рейтинг: 0 / 0
23.03.2017, 18:15
| Ответить | Цитировать | Написать  
Оптимальное создание таблиц для выборки
    #39426034
blur
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
blur
Гость
White OwlТам не только количество записей играет роль, но и ширина записей (количество колонок в таблице).
Как раз пришел к тому, что придется туда еще несколько текстовых столбцов добавить.)
White OwlКроме увеличения количества записей можешь для наглядности увеличить-уменьшить ожидаемую выборку в between.
Это я все делал и с индексом и без него. Все запросы выполнялись в интервале от 0.003 - 0.015 сек.
White OwlА документацию читать не модно? https://www.sqlite.org/eqp.html
Еще как модно. Только вот начинаю читать про одно, а заканчиваю совершенно про другое. Новые термины отвлекают пока.
Вот, а за конкретную ссылку большая благодарность (жаль, что специальной кнопочки нету).
...
Рейтинг: 0 / 0
23.03.2017, 19:06
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Оптимальное создание таблиц для выборки
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=54&msg=39426001&tid=2008518]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 314ms
total:  440ms
созд. / загр.: 440ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]