ReSQL.ru
     
powered by simpleCommunicator - 2.0.48     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / выборка с индексом медленее, чем без индекса +
17 сообщений из 17, страница 1 из 1
выборка с индексом медленее, чем без индекса +
    #39896488
Фотография tchingiz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tchingizПривилегированный пользователь
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
create table x (
	ObjectId varchar (128),
	ObjectName  varchar (128),
	ObjectDesc varchar (128),
	GpsDate varchar (128),
	GpsTime varchar (128),
	Longitude double,
	Latitude double,
	Height int,
	Speed int
);
Insert into x values ('15B624E9-B211-457A-B570-5D0A5754380E','auto01','auto01','01.01.2012','00:00:08',0.000000,0.000000,0,0);

create index i1 on x(longitude);
drop index i1;


select * from x where Longitude > 30 and	Longitude  < 40.1;



с индесом запрос за 20 сек. выполняется, без индекса 15.

версия (виндовс 7)
3.30.1 2019-10-10 20:19:45 18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3df1b0b

данных 383 тысячи записи:


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
Begin Transaction;
--Наступна команда 
 Insert into x values ('15B624E9-B211-457A-B570-5D0A5754380E','auto01','auto01','01.01.2012','00:00:08',0.000000,0.000000,0,0);
--Наступна команда 
 Insert into x values ('15B624E9-B211-457A-B570-5D0A5754380E','auto01','auto01','01.01.2012','00:00:41',30.461410,50.366790,314,3);
--Наступна команда 
 Insert into x values ('15B624E9-B211-457A-B570-5D0A5754380E','auto01','auto01','01.01.2012','00:01:15',30.461750,50.365790,255,45);
--Наступна команда 
 Insert into x values ('15B624E9-B211-457A-B570-5D0A5754380E','auto01','auto01','01.01.2012','00:01:47',30.462040,50.365070,267,0);
--Наступна команда 
 Insert into x values ('15B624E9-B211-457A-B570-5D0A5754380E','auto01','auto01','01.01.2012','00:02:17',30.462210,50.364870,279,0);
--Наступна команда 
 Insert into x values ('15B624E9-B211-457A-B570-5D0A5754380E','auto01','auto01','01.01.2012','00:02:47',30.462170,50.364970,280,0);
.....
Commit;
...
Рейтинг: 0 / 0
29.11.2019, 15:47
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896489
Фотография tchingiz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tchingizПривилегированный пользователь
Участник
прямо в творческом тупике нахожусь.
спасите - помогите
...
Рейтинг: 0 / 0
29.11.2019, 15:48
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896503
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
а если так попробовать
Код: sql
1.
select * from x where Longitude between 30 and 40.1


или так
Код: sql
1.
select * from x where Longitude  < 40.1 and Longitude > 30
...
Рейтинг: 0 / 0
29.11.2019, 16:08
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896511
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
Посмотри разницу в планах выполнения EXPLAIN QUERY PLAN , может какие мысли возникнут.
...
Рейтинг: 0 / 0
29.11.2019, 16:21
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896523
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
Еще интересно какая часть записей выбирается:
Код: sql
1.
select count(*) from x where Longitude > 30 and	Longitude  < 40.1;


Если это более 20% записей (~80 тыс. у тебя), то тут от использования индекса только тормоза, т.к. при скане идет последовательный перебор (последовательное чтение всех записей на странице), а по индексу случайное чтение (прочитать указатель на очередную запись, прочитать нужную страницу, прочитать с нее нужную запись и т.д.).

Возможно при индексном поиске нужные страницы выдавливаются из кэша и поэтому читаются с диска по несколько раз.
Попробуй увеличить кэш :
Код: sql
1.
PRAGMA cache_size = -32000;
...
Рейтинг: 0 / 0
29.11.2019, 16:46
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896526
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
IMHO & AFAIK

Например фрагментация данных в таблице

При считывание всей таблицы - чтение происходит последовательно
При работе через индекс - чтение происходит поблочно, при этом в логической последовательности сортировки данных != физической последовательности блоков на диске/файле. Мало того, если данные не помещаются в кэш, некоторые блоки могут считываться и по несколько раз IMHO (не проверял).

Для Oracle такие вещи обходил coverage index'ом. Когда все поля из таблицы дублировались в самом индексе. В этом случае, порядок обращения совпадал с порядком следования блоков

Умеет ли SQL Lite оптимизировать обращение к таблице при наличие coverage index'а - не знаю

p.s. В некоторых СУБД бывают index organized table, но в моей версии Oracle они работали "странно". Coverage index работал лучше.
...
Рейтинг: 0 / 0
29.11.2019, 16:48
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896531
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
Из инета. Не проверял. В доке не нашел:

If you create an index on all your columns, SQLite will sometimes execute a select using only the index, but you'll be storing your data twice.

http://sqlite.1065341.n5.nabble.com/index-organized-table-compression-td2577.html
...
Рейтинг: 0 / 0
29.11.2019, 16:53
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896547
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
Скорее всего это действительно фрагментация данных приводящая к тому, что подчиняясь индексу движку приходится дергать одну и ту-же страницу таблицы несколько раз.

Чтобы увидеть скорость работы с одним только идексом, попробуй сделать индекс на (Longitude, ObjectId, ObjectName). А потом выбрку
Код: sql
1.
select Longitude, ObjectId, ObjectName from x where Longitude > 30 and Longitude  < 40.1


В этом случае все данные для запроса будут уже хранится в индексе и таблицу не надо будет трогать вообще.
...
Рейтинг: 0 / 0
29.11.2019, 17:38
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896664
Фотография tchingiz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tchingizПривилегированный пользователь
Участник
Dima T
а если так попробовать
Код: sql
1.
select * from x where Longitude between 30 and 40.1


или так
Код: sql
1.
select * from x where Longitude  < 40.1 and Longitude > 30


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

1
оба плана
Код: plaintext
1.
2.
3.
QUERY PLAN
`--SCAN TABLE x
QUERY PLAN
`--SEARCH TABLE x USING INDEX j1 (Longitude>? AND Longitude<?)
2
Данных добавил больше, там один или две трека (значит значения меняются достаточно плавно). Несколько раз добавил одно и тоже.

Код: plaintext
1.
2.
3.
4.
5.
6.
30.5 30.6 |242080|  24208
30 30.5   |1437792
30 32     |2459968
30 35     |2483584
total     |3292849| 65856

для 30.5 30.6 выбираю 7 процентов (треть от одной пятой)


Код: sql
1.
2.
3.
4.
5.
select '30.5 30.6 ', count(*), count(*)/10  from x where  Longitude > 30.5 and Longitude  < 30.6 ;
select '30 30.5   ', count(*)  from x where  Longitude > 30 and Longitude  < 30.5 ;
select '30 32     ', count(*)  from x where  Longitude > 30 and Longitude  < 32.1 ;
select '30 35     ', count(*)  from x where  Longitude  between 30 and  35.1 ;
select 'total     ', count(*), count(*)/50  from x;-- where  Longitude  between 30 and  35.1 ;




запрос '30.5 30.6 ' с индексом - 9 секунд выполняется
без индекса 8 или 7.
...
Рейтинг: 0 / 0
30.11.2019, 11:02
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896669
Фотография tchingiz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tchingizПривилегированный пользователь
Участник
White Owl

Чтобы увидеть скорость работы с одним только идексом, попробуй сделать индекс на (Longitude, ObjectId, ObjectName). А потом выбрку
Код: sql
1.
select Longitude, ObjectId, ObjectName from x where Longitude > 30 and Longitude  < 40.1


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

йопть я об этом никогда не думал.

с индексом работает 2 секунды размер бд 499м
без индекса работает 5 секунд размер бд 300м


щас сделаю дефрагментацию диска и повторю исходный запрос
...
Рейтинг: 0 / 0
30.11.2019, 11:14
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896674
Фотография tchingiz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tchingizПривилегированный пользователь
Участник
Код: sql
1.
select '30.5 30.6 ', Longitude  from x where  Longitude > 30.5 and Longitude  < 30.6 ;


1 сек с индексом, 5 сек без индекса

чуть лучше
Код: sql
1.
select '30.5 30.6 ', Longitude, *  from x where  Longitude > 30.5 and Longitude  < 30.6 ;



9 сек с индексом, 8 сек без индекса
...
Рейтинг: 0 / 0
30.11.2019, 11:59
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896689
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
Кэш пробовал увеличить? 22029078
...
Рейтинг: 0 / 0
30.11.2019, 13:22
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896717
Фотография tchingiz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tchingizПривилегированный пользователь
Участник
Dima T
Кэш пробовал увеличить? 22029078

с индексом стало на секунду быстрее 7 и 8 (без индекса).
Будем надеятся, что в сложных селектах, где я буду только айди извлекать из такой таблицы
пользы будет больше.
...
Рейтинг: 0 / 0
30.11.2019, 19:27
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896722
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
tchingiz
Dima T
Кэш пробовал увеличить? 22029078

с индексом стало на секунду быстрее 7 и 8 (без индекса).
Будем надеятся, что в сложных селектах, где я буду только айди извлекать из такой таблицы
пользы будет больше.

Попробуй понять что я спросил
...
Рейтинг: 0 / 0
30.11.2019, 20:13
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896732
Фотография tchingiz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tchingizПривилегированный пользователь
Участник
Dima T
tchingiz
пропущено...

с индексом стало на секунду быстрее 7 и 8 (без индекса).
Будем надеятся, что в сложных селектах, где я буду только айди извлекать из такой таблицы
пользы будет больше.

Попробуй понять что я спросил

от зануда.
Докладываю:
после того как я добавил кеш, стало лучше. далее, по предыдущему посту.
...
Рейтинг: 0 / 0
30.11.2019, 21:48
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896742
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
tchingiz
...
Будем надеятся, что в сложных селектах, где я буду только айди извлекать из такой таблицы
пользы будет больше.

конечно не больше.
А относительная скорость почти наверно ухудшится.
что-то вам про индексы узнать было бы полезно.
...
Рейтинг: 0 / 0
30.11.2019, 23:00
| Ответить | Цитировать | Написать  
выборка с индексом медленее, чем без индекса +
    #39896743
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
booby
tchingiz
...
Будем надеятся, что в сложных селектах, где я буду только айди извлекать из такой таблицы
пользы будет больше.

конечно не больше.
А относительная скорость почти наверно ухудшится.
что-то вам про индексы узнать было бы полезно.


это в расчете на запрос вида

Код: sql
1.
select ObjectId  from x where Longitude > 30 and	Longitude  < 40.1;


при условии, что индекс создан по значениям Longitude
...
Рейтинг: 0 / 0
30.11.2019, 23:02
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / выборка с индексом медленее, чем без индекса +
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=54&fpage=2&tid=2008371]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 30ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 2ms
others: 245ms
total:  372ms
созд. / загр.: 372ms
x
x
Закрыть


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