ReSQL.ru
     
powered by simpleCommunicator - 2.0.37     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / У меня получается что индексы не ускоряют выборку...а наоборот
30 сообщений из 30, показаны все 2 страниц
  все  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182227
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
Имеется небольшая таблица расчет в которой 3 поля:
ни одно поле не индексировано.
таблица содержит 1000000 случайных записей,состоящих из чисел
в диапазоне от 1 до 999999
создаем запрос . суть которого в данной картинке

делаем серию запросов с индексом и без индекса
получаем результаты (в секундах)
без индекса с индексом
0,562 4,212
0,568 4,176
0,556 4,186
0,557 4,183
0,558 4,168

Вопрос: почему создание индекса не ускоряет запрос?
...
Рейтинг: 0 / 0
01.03.2016, 11:13
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182241
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
вот пример результата запроса с индексом
...
Рейтинг: 0 / 0
01.03.2016, 11:20
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182312
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
avedeo,

какой именно создается индекс - предлагается угадать самостоятельно?
планы запросов, показывающие использование/неиспользование индекса где? или мы, как истинные джентльмены, верим друг другу на слово?
...
Рейтинг: 0 / 0
01.03.2016, 11:52
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182318
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
...
Рейтинг: 0 / 0
01.03.2016, 11:56
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182347
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
Добрый Э - Эх,
поле id-расчет автоинкрементное, первоначально предполагалось сделать индексом его,
но увы ...оказывается так нельзя делать..SQLiteman дал понять, что это неправильно
(я недавно ...с неделю только начал изучать SQLite), поэтому пришлось создать
индекс по полю "количество".
...
Рейтинг: 0 / 0
01.03.2016, 12:12
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182428
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VSVLAD
Участник
avedeo,

А почему не включить в индекс поле "цена", по нему также идёт отбор
...
Рейтинг: 0 / 0
01.03.2016, 12:52
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182480
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
VSVLAD,
делал индексы и по двум полям "количество" и "цена"... но результат тот же.
...
Рейтинг: 0 / 0
01.03.2016, 13:26
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182512
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
VSVLAD,
Только что добавил еще 1000000 записей...всего стало 2000000 записей,сделал 2 индекса
и вот результаты:
Выборка из 2000000 записей (в секундах)
при одном индексе при 2 индексах без индексов
9,68 .........................1,072.................0,972
9,315........................1,029.................1,057
9,312.........................1,032,,,,,,,,,,,,,,,,,1,049
9,321.........................1,063.................0,988
...
Рейтинг: 0 / 0
01.03.2016, 13:44
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182515
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
...
Рейтинг: 0 / 0
01.03.2016, 13:46
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182519
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VSVLAD
Участник
avedeo,

приложите определение таблицы, и немного тестовых данных (в виде insert into), попозже сгенерирую лям записей и попробуем выяснить, всё ли так оно выглядит
...
Рейтинг: 0 / 0
01.03.2016, 13:48
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182520
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
...
Рейтинг: 0 / 0
01.03.2016, 13:49
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182522
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
...
Рейтинг: 0 / 0
01.03.2016, 13:51
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182534
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
VSVLAD,таблица заполнялась с помощью SQLiteStudio...
...
Рейтинг: 0 / 0
01.03.2016, 13:58
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182561
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
VSVLAD,то что таблица содержит 2000000 записей...это точно
...
Рейтинг: 0 / 0
01.03.2016, 14:19
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182709
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VSVLAD
Участник
avedeo,

Можете приложить не скриншоты, а SQL? Перебивать с картинки нет большого желания
...
Рейтинг: 0 / 0
01.03.2016, 15:46
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182758
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
VSVLAD,
-- создаем таблицу РАСЧЕТ

CREATE TABLE "расчет" (
"id-расчет" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"количество" REAL NOT NULL,
"цена" REAL NOT NULL
)

--создаем индексы

CREATE INDEX "индекс-общий" on расчет (количество ASC, цена ASC)

--создаем запрос на подсчет числа записей в таблице

select count(*) from расчет

-- создаем запрос на выборку из двух полей+вычисляемое поле

select *, количество*цена as сумма
from расчет
WHERE количество = '774'
OR цена = '161616'
group by количество,цена,сумма
order by сумма desc
...
Рейтинг: 0 / 0
01.03.2016, 16:17
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182824
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VSVLAD
Участник
avedeo,

У меня ваш запрос с вашей схемой отрабатывает: ~1500 мс
Ваш план:
selectidorderfromdetail000SCAN TABLE расчет USING COVERING INDEX индекс_общий000USE TEMP B-TREE FOR GROUP BY000USE TEMP B-TREE FOR ORDER BY
Ваш запрос:
Код: sql
1.
2.
3.
4.
5.
6.
select *, количество*цена as сумма 
from расчет 
WHERE количество = '774'
      OR цена = '161616'
group by количество,цена,сумма
order by сумма desc



Что делаю:
1) Убираем кавычки, т.к. у нас числа, а не текст. Получаем в среднем: 920 мс
2) Если добавить ваш индекс, то 853 мс
3) Удаляем ваш индекс и создаём два индекса по каждому полю. Получаем: 16 мс
CREATE INDEX idx_расчет_колво on расчет(количество)
CREATE INDEX idx_расчет_цена on расчет(цена)

Итоговый план:
selectidorderfromdetail000SEARCH TABLE расчет USING INDEX расчет_колво (количество=?)000SEARCH TABLE расчет USING INDEX расчет_цена (цена=?)000USE TEMP B-TREE FOR GROUP BY000USE TEMP B-TREE FOR ORDER BY
Запрос тот же, но без кавычек у значений.
Также смысл запроса и что он выдаёт, не особо понял, поэтому изменять и модифицировать не стал
...
Рейтинг: 0 / 0
01.03.2016, 17:09
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39182855
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
VSVLAD,
Спасибо, что изучили вопрос...смысл запросов в измерении скорости работы SQLite на моем компе
хотя бы в простых случаях, заметил, что в зависимости условия выборки сильно разнится время
меньше всего при условии =... больше всего время запроса при условии "не равно" <>...
ну это вполне объяснимо.
...
Рейтинг: 0 / 0
01.03.2016, 17:27
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39183169
Фотография PPA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PPA
Участник
avedeo
А зачем тут такое странное использование group by?
...
Рейтинг: 0 / 0
02.03.2016, 02:59
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39183252
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
Не улавливаю... в чем странность...я хочу видеть результат запроса именно в такой последовательности
притом довольно логичной скажем для документа типа например товарный чек: кол-во, цена, сумма.
Или вы имеете ввиду что GROUP BY ... стоит не на своем месте...да вроде тоже логично...группируем, потом сортируем.
...
Рейтинг: 0 / 0
02.03.2016, 09:19
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39183328
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
VSVLAD,
вообщем у меня получается, что без индексов база работает быстрее... и все
один и тот же запрос...условие и число не меняю
- с двумя индексами по двум полям -10,811; 10,864 секунды
- без индексов -3,831; 3,689 секунд
я понимаю, что так не должно быть...но увы...
...
Рейтинг: 0 / 0
02.03.2016, 10:29
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39183340
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
Создал уникальный индекс по 3 полям...включая автоинкрементное
перед запросом переиндексировал...чтоб уж точно актуальный был

CREATE UNIQUE INDEX idx ON расчет ("id-расчет" ASC, количество ASC, цена ASC)

при тех же параметрах запроса результат( в секундах) 3,722; 3,655; 3,641
...
Рейтинг: 0 / 0
02.03.2016, 10:40
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39183785
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VSVLAD
Участник
avedeo,

Я вам показал, у меня 2 млн отрабатывает за 16 мс, значит "один и тот же запрос" не есть один и тот же. Я надеюсь, вы убираете кавычки и индексы создали 2 разных индекса по 1 полю, а не 1 индекс с двумя полями
...
Рейтинг: 0 / 0
02.03.2016, 14:37
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39183852
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
VSVLAD,
и кавычки убрал...и два индекса сделал отдельных

-- Описать IDX2
CREATE INDEX "idx2" on расчет (цена ASC)

-- Описать IDX1
CREATE INDEX "idx1" on расчет (количество ASC)

самому не нравятся такие результаты...но факты... упрямая вещь
...
Рейтинг: 0 / 0
02.03.2016, 15:15
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39184228
MrCat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MrCat
Гость
В WHERE два условия (на цену, на количество) через ИЛИ. Композитный индекс (кол-во, цена) хорошо сработает (при должной селективности) для условия на количество. Для условия на цену, добавленное через ИЛИ, он не сработает, и придётся делать полный перебор данных таблицы. "COVERING" в плане запроса означает, что для выдачи данных по запросу достаточно самого индекса (к таблице обращаться не нужно). Т.е. Covering index означает здесь не индексированный доступ, а всего лишь источник данных.

Попробуйте добавить в таблицу новое поле, не входящее в индекс. Движок мигом перестанет прикидываться шлангом и покажет SCAN TABLE в плане для "select *", а не USING INDEX!

--

И да, индексы - не панацея. Даже если сделать по индексу на каждое поле, то выигрыш по сравнению с полным перебором они дадут при высокой селективности каждого, и если каждое ИЛИ-условие в WHERE предполагает небольшую выборку.
...
Рейтинг: 0 / 0
02.03.2016, 19:41
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39184538
Фотография PPA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PPA
Участник
avedeoНе улавливаю... в чем странность...я хочу видеть результат запроса именно в такой последовательности
притом довольно логичной скажем для документа типа например товарный чек: кол-во, цена, сумма.
Или вы имеете ввиду что GROUP BY ... стоит не на своем месте...да вроде тоже логично...группируем, потом сортируем.

А это у вас игрушечная база?
модель данных какая-то странная.
у этой таблицы нет внешнего ключа.

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

Вот представьте, что у вас будет в таблице 2 пары с одинаковым кол-вом и ценой, но товар этот разный.
в результате группировки у вас что получится?

group by без агрегирующих функций выглядит странновато.
и фактически является аналогом distinct

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

Результаты разбора запроса в Oracle
из примеров видно что ваш вариант вообще не компилируется

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 


SQL> 
SQL> CREATE TABLE "расчет" (
  2  "id-расчет" INTEGER NOT NULL,
  3  "количество" REAL NOT NULL,
  4  "цена" REAL NOT NULL
  5  )
  6  /
Table created

SQL> 
SQL> select *, "количество" * "цена" as "сумма"
  2  from "расчет"
  3  WHERE "количество" = 774
  4  OR "цена" = 161616
  5  group by "количество","цена","сумма"
  6  order by "сумма" desc
  7  /
select *, "количество" * "цена" as "сумма"
from "расчет"
WHERE "количество" = 774
OR "цена" = 161616
group by "количество","цена","сумма"
order by "сумма" desc
ORA-00923: FROM keyword not found where expected

SQL> 
SQL> select "количество" * "цена" as "сумма"
  2  from "расчет"
  3  WHERE "количество" = 774
  4  OR "цена" = 161616
  5  group by "количество","цена"
  6  order by "сумма" desc
  7  /
     сумма
----------

SQL> 
SQL> select "расчет".*, "количество" * "цена" as "сумма"
  2  from "расчет"
  3  WHERE "количество" = 774
  4  OR "цена" = 161616
  5  group by "количество","цена","сумма"
  6  order by "сумма" desc
  7  /
select "расчет".*, "количество" * "цена" as "сумма"
from "расчет"
WHERE "количество" = 774
OR "цена" = 161616
group by "количество","цена","сумма"
order by "сумма" desc
ORA-00904: "сумма": invalid identifier

SQL> 
SQL> select "расчет".*, "количество" * "цена" as "сумма"
  2  from "расчет"
  3  WHERE "количество" = 774
  4  OR "цена" = 161616
  5  group by "количество","цена"
  6  order by "сумма" desc
  7  /
select "расчет".*, "количество" * "цена" as "сумма"
from "расчет"
WHERE "количество" = 774
OR "цена" = 161616
group by "количество","цена"
order by "сумма" desc
ORA-00979: not a GROUP BY expression

SQL> 
SQL> select "количество" * "цена" as "сумма", max("id-расчет")
  2  from "расчет"
  3  WHERE "количество" = 774
  4  OR "цена" = 161616
  5  group by "количество","цена"
  6  order by "сумма" desc
  7  /
     сумма MAX("ID-РАСЧЕТ")
---------- ----------------
...
Рейтинг: 0 / 0
03.03.2016, 10:31
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39184570
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
PPA, спасибо огромное...вы сподвигли меня на изучение индексов...оказывается еще хинты
предписывающие оптимизатору движка директиву() hint /*+ .... index(idx1) index(idx2) ... */...
использовать (как я понял...тема еще только изучается) только индексы...а потом еще есть
какие-то суррогатные индексы, реверсивные индексы...еще предстоит все это понять, попрактиковать
и усвоить на практических примерах.

Да...изначально...таблица РАСЧЕТ (она ни чем не связана ) была создана с узкой целью
определить вычислительные возможности движка

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

- ну и то же самое...но с различными группировками и сортировками

вот как-то так...
...
Рейтинг: 0 / 0
03.03.2016, 11:04
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39237422
economistalex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
economistalex
Гость
Как-то собирал отовсюду "мурзилку" по Индексам, вот она:

ВАЖНО: Запомните "Правило пятнадцати": При выборе из таблицы более 15% её строк - полный просмотр быстрее индекса и наоборот.

Всегда создавайте индексы на столбцах, которые:
- ЧАСТО используются в условиях WHERE
- ЧАСТО используемые для соединения таблиц (WHERE и JOIN)
- содержат МАЛО одинаковых значений (разнообразны), но востребованы в WHERE
- РЕДКО изменяются (индексы долго перестраиваются)
- РЕДКО попадают в INSERT, UPDATE и DELETE
- РЕДКО подвергаются функциям типа CAST, LOWER итд.

Еще стоит знать когда имеющийся Индекс игнорируется SQL-запросом. Если у вас много SELECT-запросов с условиями, перечисленными ниже - не создавайте индекс, т.к. он будет просто мешать:
Поле1 >, <, >=, <=, <>, != Поле2
Поле IS NULL, Поле IS NOT NULL
Поле NOT IN (Значение1, Значение2)
Поле1 LIKE '%строка' (но для Поле1 LIKE 'строка%' и Поле1 LIKE %pattern%' - индекс используется)
NOT EXISTS подзапрос

Чтобы принудительно отключить индекс, добавьте пустое выражение ||'' к текстовому полю (или +0 к числовому) в условии WHERE: SELECT * FROM КОНТР WHERE ИНН||''="2317777777"
...
Рейтинг: 0 / 0
17.05.2016, 14:23
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39237485
MrCat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MrCat
Гость
Ух, ё, какая жирная, безапелляционная мурзила, хорошо, наверное, пионеров пугает! Предлагаю свою мурзилку, покороче, поскромней: если Вам не хватает мозгов, чтобы оценить эффективность индекса умозрительно - оцените её посредством тестов.
...
Рейтинг: 0 / 0
17.05.2016, 15:16
| Ответить | Цитировать | Написать  
У меня получается что индексы не ускоряют выборку...а наоборот
    #39240442
Фотография avedeo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
avedeo
Участник
economistalex,спасибо за экстрактность...как сказал Керри Маккейну "На самом деле все несколько сложнее..."
...
Рейтинг: 0 / 0
20.05.2016, 21:05
| Ответить | Цитировать | Написать  
30 сообщений из 30, показаны все 2 страниц
  все  
Форумы / SQLite [игнор отключен] [закрыт для гостей] / У меня получается что индексы не ускоряют выборку...а наоборот
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=54&tid=2008598]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 30ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 55ms
get tp. blocked users:
 1ms
others: 290ms
total:  418ms
созд. / загр.: 418ms
x
x
Закрыть


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